Track Changes in Microsoft Excel–Essentials

One of my wife’s colleagues recently asked about whether it’s possible to track changes in an Excel spreadsheet. It certainly is, though it works a little differently than in Word, say.
Turning the feature on in Excel is quite easy–simply go to the Review tab in the Ribbon, slide over to the Changes group, and click Track Changes.
Review Tab, Track Changes
Almost immediately, we see one of the differences between this and the Word version–we are told this will put the document into Shared mode, allowing others to open and edit the file at the same time. We also want to decide which specific changes to highlight.
Highlight Changes dialog box
Usually it’ll be When and Who. By not checking Where, we’re saying we want to see where *all* changes are being made, which is normally desirable. And we almost always want to track changes on screen, though it’s possible to list changes on a new sheet. (Sometimes, for business situations, it’s quite helpful to do the latter, but being able to track and accept or reject onscreen is often immediately useful.)
Once the feature is on, any changes we make will be tracked. A marker will appear at the upper left of the cell(s) in question, to let us know something’s different. Whenever we save, the markers disappear.
More names added
But the program still tracks changes. Whenever we’re ready, we can review them and decide to officially accept or reject them.
Accept-Reject dialog box
And if something changes after that..no matter how many times, the feature will still follow along.
Uncle Vlad added to list

So we can definitely track changes in Excel. The nature of the program requires that it function a little differently than in other members of the Microsoft Office, but not so much that the average user would have trouble with it.

The one downside to tracking changes in any of the Office programs is the feature is a little memory intensive. This isn’t nearly as much of a problem as it might have been a few years ago, what with the faster processors and less expensive memory we can get now.

Access vs Excel—Which should we use for what?

Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even when it’s working fine.

So let’s just ask the question: When should we use each one?

To answer this, we need to understand what each one can do. Excel is a spreadsheet program. Even though information on one sheet can talk to info on another, it’s primarily meant to do math-related things. So if all you need is the equivalent of a sheet of graph paper to visually organize, say, a personal or house inventory, Excel does pretty well. You could use Word for this same purpose, and have about the same amount of work, if you don’t want to use Excel. Formatting and other aesthetic things are only about as hard as in Word, so it’s almost as if you built a giant table in Word and jotted stuff in it. Or if you want to budget monthly, quarterly, and annually, and have all of those update each other, great.

On the other hand, if you need to track something more complicated, such as information on the employees of a small (or large) company, you may run into trouble with Excel. The problem is, you may need to have three or four kinds of information that all relate to the employees, but are not directly related to each other: Personal info (home address, birthday, family), office info (building, floor, office number, phone number, business email), health plan (HMO, policy type, policy number), and maybe transportation (car make and model, parking spot, bus route, commute time). Yet all of these need to tie together, as it were. You might have each of these connect to the others through, say, the employee ID number or Social Security number—make that ID part of each of the four categories so they have a common element. This is where the term “relational database” comes from—the different categories of information nevertheless relate to each other, like a baseball team where each player has a separate job, but all cooperate to play and win.

If this idea makes sense, that we separate the types of information but allow them to relate through one item, then you understand one of the main concepts of the relational database. Like Access.

So the basic answer to the question is, If you have just one kind of information (or several that are unrelated), you can store each chunk on a separate worksheet in Excel and probably have no trouble. But if the different subsets (or as Access would call them, data tables) are related after all, using Access to store the information and work with it would actually be worth the extra time and effort. As a bicycle is good for getting around by yourself, but a pickup truck can haul some serious cargo by comparison, so Excel and Access. (Sort of.  🙂  )

As a postscript, the reason I’m not bringing up PowerPivot here—which lets us do some database-ish things in Excel—is that it’s a different tool again from Access, though it is useful. And not everyone knows about it, or how it works. We do teach it at SkillForge, but again, it’s a somewhat specialized tool, whereas Access is a more general database application and has more multi-user features, making it more suitable for business database work.

How to Use COUNTBLANK in Excel

Some of the Excel functions, such as IF, come into play all the time, even outside business. They’re versatile and can do a lot. But others seem a little abstruse, or out in left field.

One question I occasionally get from business people in Excel is something like, “How do I make sure someone has filled in all the cells where I need data to, say, calculate an average correctly?” (Think “tax form” or W-4, for example. You want to make sure certain spaces are filled in regardless.)

In some situations, you might use a function like ISERROR, and incorporate it with IF to test if there IS an ERROR when you perform a calculation:

=IF(ISERROR(AVERAGE(D2:D10))…

…so that if performing the calculation glitches, you can have the formula show a message to this effect.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”…

Otherwise, perform the calculation.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”,AVERAGE(D2:D10))

Using ISERROR with AVERAGE

 

But this doesn’t quite solve the problem, because even if just one of the cells has data in it, there won’t be an error as far as Excel is concerned.

Formula finds one cell with data and calculates average

 

So we need to call a function that looks for ANY cells being empty, and lets us know. It’s called COUNTBLANK. (Sounds like some kind of strange nobleman–Count Blank, from some tiny hamlet somewhere….)

 

=IF(COUNTBLANK(D2:D10)>0,”Missing Data in D2 thru D10″,AVERAGE(D2:D10))

COUNTBLANK finds missing data and tells user

 

What it does is pretty simple, though. It COUNTs the BLANKs in a range, and can let us know how many there are. IF (as you see in the above formula) there are any (“COUNTBLANK(D2:D10)>0”), we want to see the TRUE result from the IF (the message about missing data); if not–that is, if the test is FALSE–then we want it to calculate the average. We’ve told the program to perform the calculation only IF the COUNT of BLANK cells is NOT greater than 0, and let us know if there are any blanks so we (or the user) can correct this.

COUNTBLANK finds no missing data

 

Of course, guaranteeing that only numbers get filled in is another matter. But there are a few different ways to take care of this, such as Data Validation. The important thing is, MISSING data are a problem, as the result isn’t an accurate one. And letting the user know about this is the big thing.

Excel – Hide Records with Zeroes in Pivot Table Calculations

One of the most asked questions of beginning, and sometimes seasoned, pivot table users is “How do I hide the entries in a pivot table whose totals equal zero?”  On first blush, this seems like an easy feat, but users quickly discover that it’s not as easy as predicted.  There are ways to sort the source data and then exclude the entries with zero values, but that task of sorting and filtering the source data would have to be performed each time the source data is updated.  This is not an appealing prospect.

There is actually a very easy way to not display pivot table records that equal zero. (more…)

Excel Hidden Camera Tool – Great for Dashboards

If you are an Excel user who likes to create charts, design dashboards, or just likes to play with neat toys in Excel, this tutorial is going to be right up your alley.

Excel contains (in a super-secret place) a hidden camera.  “But why would I need a hidden camera in a spreadsheet program?”  I’m glad you asked.  If you have ever created a chart on one sheet, but you need the chard displayed simultaneously on a different sheet, and you don’t want to make two of the same thing, the camera tool will solve this problem.

First thing’s first; we have to find the camera before we can put it to creative use.

(more…)

12 Excel Keyboard Shortcuts for Every User

Keyboard shortcuts are a great way to improve the speed at which documents are built, regardless of the application.  It seems like there is a keyboard shortcut for just about every feature Excel contains; and there may be that one guru in the office that knows them all.  But most of us fall somewhere between Guru and Labrador retriever (hopefully, closer to the former.)

The good news is that it’s not an “all or nothing” proposition when it comes to keyboard shortcuts.  Knowing just a few of the most productive keyboard shortcuts will serve you far better than knowing none at all.

So let’s get this show on the road!

  1. CTRL+SHIFT+L – Turn On/Off Filter Controls

Filters are of tremendous use when analyzing large numbers of records in a table, but you are only interested in a select set of records that met a specific criteria.  Activating your filters is just a CTRL-SHIFT-L away.  This keyboard can also be used to turn off all of the filters and display the entire list.  (Filters are on by default when you convert a straight table to a Data Table, and not always desired.)  Finally, if you hit the “L” key twice (CTRL-SHIFT-L & L) you can effectively clear the current filters to start fresh with a new filter query. (more…)

Excel – Conditional Formatting with Subtotals

If there has ever been a more “You’ve got your chocolate in my peanut butter!” moment, it’s the blending of Conditional Formatting with the Subtotals tool in Excel.

If you have ever used the Subtotals tool to group information you have probable been impressed with its ability to group data by some changing event (like States) and have those groups aggregated and then structured into a collapsible outline.

Before Subtotals

CondFmt1

After Subtotals

CondFmt2

But the one shortfall when it comes to the Subtotals tool is that there are no built-in artistic styles that can be applied to give the list a bit of pizazz. (more…)

Using Excel MODE Function to Return a Text Response

Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers.  But what if you want to return the most frequently occurring word in a list of words?

MODE with Numbers

Using the MODE function in Excel is quite simple; you point to a list of numbers and MODE will tell you which number occurs the most often.

Mode1

In this list, the number “4” appears more often than any other number.

MODE with Words

As you can see, the MODE function does not work very well when pointing to a list of words.

Mode2

The function returns a “#N/A” error message.

Not to fear; MODE can be made to return words, but it take the combined efforts of SEVERAL functions, none of which are MODE!  (How odd does THAT sound?) (more…)

Excel – Mixed Pivot Table Layout

Excel – Mixed Pivot Table Layout

Microsoft Excel Pivot Tables is one of the greatest inventions known to man; second only to those buttery mints they bring you in fancy restaurants.  Even the greatest of ideas are not without their issues.  Take for example the Report Layout feature in Pivot Tables.  Excel gives the user three options with which to display hierarchically (wow; that’s a hard word to spell) related data.

  • Compact Form
  • Outline Form
  • Tabular Form

Let’s examine what makes each layout unique.

Compact Form

All row-based data is combined into a single column, one row per line item, and indented to reflect the position within the hierarchy.

PivotLayout01 (more…)

Excel – Convert Names to Email Addresses

Converting Names into Email Addresses

Suppose you have a list of names, perhaps a roster of employee names, and you wish to generate email addresses for these individuals. If you work at a company that has an established standard for email addresses (i.e. first initial of first name with last name) then you have a few options. The preferred strategy depends largely on the version of Excel you are using as well as the naming pattern used in the emails addresses.

Flash Fill (Excel 2013 / Excel 2016)

If you are not familiar with Flash Fill, this tool allows you to type a pattern next to existing data and Flash Fill will repeat the pattern for the remaining data but on a per-record/per-line basis.

Let us take a look at the following example:

You have a list of first and last names and you wish to convert those names to an email format that takes the first letter of the first name, adds a “dot”, then adds the last name with an “@” sign and the company domain name. If we had an employee named “Fred Smith” who worked at “widget.com”, we would need to assign the email address “f.smith@widget.com” to the user.

Imagine a list like the following:

Convert01

(more…)

What’s New in Microsoft Office 2010 – Part 1 of 6

With every new release of Microsoft Office (or really any software product for that matter) users of a previous version ask themselves the question “should I upgrade?” And, as always, the answer is…it depends. I’ve collected a few of the new features of Office 2010 to hopefully help you make your decision. However, if you’re one of the many folks who didn’t move to Office 2007 because of the new Ribbon interface – you may want to seriously consider Office 2010. It corrects some of the confusion introduced by the Ribbon interface and adds quite a few useful new features as well. In this post, we’ll explore some of the new features that are common to most, if not all, of the Office 2010 applications. (more…)