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 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:


…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))



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.

Microsoft Project: Formatting a Gantt Chart for Summary Task

Recently a student posed a question during a Microsoft Project class. How does one format the bars of a Gantt chart to look the same for each unique summary task? Something that may look like this.


It begins with creating a custom flag field for each summary task. In Microsoft Project a flag field is a basic Boolean value. A simple yes/no field to identify further action. They are created using the Custom Fields button on the Format tab within the Gantt Chart Tools of the ribbon.


The Custom Fields button opens a separate dialog box with many options. Our first choice will be to select flag from the drop down list of data fields in the upper right-hand corner. We are presented with twenty (20) flag fields. Our example uses five (5) and each one needs a unique name.


That can be accomplished by selecting a flag, clicking rename, and supplying the new value.


Once our five (5) flags have been named we can click OK to close the Custom Fields window.


Next we need to mark our tasks as active (yes) or inactive (no) using the flags we just created. Insert a new column in the sheet view of the Gantt chart using one of our flag fields.


The entire column defaults to no but we will select the first task of our named summary and change its value to yes.


Once selected we can use the auto-fill handle (just like in Excel) to copy our yes across the remaining subtasks.


We will insert a column for each of our summary tasks and repeat the process described above.


The great thing is, once the tasks have been flagged we can hide the additional columns we just inserted.


At this point we are in the home stretch. There is only one more step necessary to finish. We need some custom bar styles to match our flagged tasks and that is done by choosing the Format tab within the Gantt Chart Tools of the ribbon and selecting Bar Styles from the Format button in the Bar Styles group. You can also double-click a blank area within the chart side of the Gantt Chart View.


In the Bar Styles dialog box we will scroll to the bottom of the list and add a new entry for each of our summary tasks using the name we gave to each flag. Shape, pattern, and color can be applied to each group and of course the flag number will be selected in the Show For … Tasks column.


Once complete, clicking OK will supply our newly flagged and formatted Gantt bars with the shape, pattern, and color we just set up.


Access 2013: Creating a Calculated Field

Often times the information you need isn’t stored as a field within a database. As a matter of fact, certain fields make more sense to calculate whenever they are needed instead of storing the value in a table.

Microsoft Access 2013 makes this very easy.

To create a calculated you will need a query in Design View. Select the Create tab and in the Queries group click Query Design button. The QBE (Query by Example) window opens and the Show Table dialog box is ready for you to add the table or tables you need.



Once the table / tables are in place you can insert the fields necessary for your query.


The calculated field will be entered in the next available column. It is often easier to see what you are typing if you open the Zoom window. That can be accomplished by Right-clicking the open field and selecting Zoom from the Context Menu.


With the Zoom window open we can enter our calculation. The information to the left of the colon represents the caption we want at the top of the column. The information to the right of the colon is our calculation. In this case it is the curUnitPrice field. No table name is necessary because we are only using one table. The field name is wrapped in a pair of square brackets and is being multiplied by 0.1.


Once the calculation is completed click OK on the Zoom window, save the query, and it is ready to run.



Access 2013: Saving a Filter as a Query

You find yourself working in an Access table filtering out some unnecessary data and it dawns on you this filtering is something you’ll end up doing frequently. It would make sense to store this process as a query in the database.




Okay, so now I have to switch over to the Create tab and design a new query based on the filter I just applied. Or do I? You can actually save the filter you just applied as a query. The process is simple.

Go to the File tab and choose Save As > Save Object As > Save As.


Once you click the Save As button you will be prompted to name the results as either a query, form, or a report.


It really is that simple. The filter you just ran is now a query and can be used whenever necessary.



Changing Microsoft Projects Default Task Duration

In a recent Microsoft Project class a question arose concerning the default task duration, which is set in days. This student had recently been working on a deployment project and the default duration was too broad. It needed to focus on a smaller time-frame and it made perfect sense to set the task duration to hours.

An excellent question and the answer couldn’t be easier.


To change the default duration for the tasks in a project we need to use the backstage options Microsoft Project makes available. By choosing the File tab and selection Options we are taken to the Project Options dialog box.


Once there we are presented with eleven options along the left side of the dialog box. Scheduling is the third option on the left and the one we are looking for.


In the Schedule section we will find Scheduling options for this project, and Duration is entered in will reveal the five choices we have:  minutes, hours, days, weeks, and months.


Also notice that the Scheduling options for this project can be set for only this project or for All New Projects.

Once the duration has been changed simply click the OK button in the lower right corner and you will be returned to you open project. Any value entered in the duration column will now default to hours unless you specify otherwise.



Adding Power View to the Excel Ribbon

In recent years Microsoft has included several add-ins to boost Excels usage in the world of Business Intelligence. They include Power Map, Power Query, Power Pivot, and our topic of discussion today, Power View for Excel 2013.

Each of these ship with the Professional Edition of Excel 2013 but need to be added through the Options Panel. After going through this process I was more than surprised to find Power View was still unavailable. So this posting will help those of you experiencing the same dilemma I faced recently.

First things first. This is what my Insert tab looked like after the Power View add-in was…added in.


Normally there would be a Reports group that included one control, Power View. As you can see it doesn’t appear on this tab or any other tab.

Here are a couple of additional screen captures to show that it had been selected and does show up in the Add-In area of the Options Panel.



I had gone through the necessary steps just as I had with Power Pivot, Power Map, and Power Query. They were all working yet Power View wasn’t available.

It took a while but the solution was found in a posting dealing with Excel 2016. Those steps are what we will go through now.

First we are going to customize the ribbon by adding a Reports group to the Insert tab. That is done by going to the File tab and selecting Customize Ribbon.


Make sure Insert is expanded and selected under Main Tabs, and then click the New Group button under the Customize the Ribbon section on the right side of the panel.


Now we will rename the group by clicking the Rename button in the same area as the New Group button.


Now we need to find Power View in the Choose Commands from section on the left. It will be in the Commands not found in the ribbon menu option. It is Insert a Power View Report.


That’s it. Click on OK at the bottom of the Options Panel and head back to the Insert tab. There it is on the far right side of the ribbon.

One additional thing, you will need Silverlight installed for Power View to work.




Microsoft Word – 10 Features that will Improve Your Workflow

With so many features packed inside each Microsoft application, it’s easy to overlook some that can really add value to your daily workflow.  Below is a list of some of the features you may have never noticed but will quickly become part of your daily routine.



Styles are not only a great time-saver, but they form the basis of a variety of other Word features.

Styles allow you to assign in bulk a variety of different formatting attributes, like font size, style, color, alignment, line spacing, borders, shades, indentation, etc…  The list is quite voluminous.  Once styles are applied to text, the text can then be bulk-updated simply by changing the style.  If you modify the style, Word will automatically apply the change to all text whose appearance was created from the style.  Imagine the time saved when making minor alterations like changing the font size of all headings.  Change the style and all of the headings are updated to match.

Once you have your styles in place, you can now also navigate more effectively through your document via the Navigation Pane.  Any text formatted with a heading style will appear as a list entry.  This will become one of the greatest time-savers when navigating large documents.

image (more…)

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…)

Microsoft Project – Display Estimated Durations with a Different Color in a Gantt Chart

When working with tasks in a project, it is common practice to display a duration as an estimate.  Displaying an estimated duration prepares the viewer for possible changes in scheduling.  An estimated duration takes the form of a question mark placed after the declared duration.


The issue is that some project viewers fail to notice the question mark; then when durations are updated, project viewers wonder why things have changed.  One way to ensure that people’s attention is drawn to the estimated durations is to change the color of the Gantt bars to reflect an estimated status.  There is no built-in state for displaying estimated durations in a separate color, but with a few short clicks this behavior can be achieved. (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.


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


After Subtotals


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.


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.


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 “”, we would need to assign the email address “” to the user.

Imagine a list like the following:



Understanding Task Dependency Types in Microsoft Project

Dependency Types in Microsoft Project

When creating schedules in Microsoft Project the first thing that a Project Manager would typically do is to input the tasks involved in a project. These tasks then need to be linked to show the relationship between them. These links create task dependencies.

There are 4 different types of task dependency:

  • Finish-to-Start (FS): The finish date of one task drives the start date of another.
  • Start-to-Start (SS): The start date of one task drives the start date of another.
  • Finish-to-Finish (FF): The finish date of one task drives the finish date of another.
  • Start-to-Finish (SF): The start date of one task drives the finish date of another.


Excel Pivot Tables Filter by Values

If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering.  The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data set.


But what do you do if you wish to filter by the Value-based items?  In other words, the numbers in the “connect the dots” area where row and column choices intersect.  These, on first glance, don’t appear to have sorting and filtering controls available.


Rest assured, they do exist; you just have to dig a bit to find them.


Excel Lookup with Dynamic Input

VLOOKUP is great for returning information from a database, but one of the limitations is that the return information is static.

What if the user wishes to look for certain data one day but different data another day?  This would require either two different sets of VLOOKUP functions or the functions would need to be reprogrammed.

In the database below, the user would wish to return address information in one scenario, but return financial information in another scenario.

Suppose there are times when the user requires a mixture of the two; that would require a third set of VLOOKUP functions. This could become an ever evolving set of work.


Automatically Refresh Excel Pivot Tables

Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit.

However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes.

Most user’s see this as a glaring lack of functionality. There is, however a very good reason why pivot tables do not automatically update.


Office 2013 – Recover Unsaved Documents

Admit it; you’ve done this more than once.  You open an Office application like Word or Excel and type out your next great novel or number-crunching masterpiece.  Then, in a state of haste, you start closing windows and accidentally close the one window you intended to leave open.  One second later you realize, this was the one windows you shouldn’t have closed.  All is lost… or is it?


Rename Files Fast with the Tab Key

If you scan documents from a network scanner (like the giant printer down the hall) and have the scan sent to your email, you are probably less than thrilled with the name that the scanner gives the file.  The name is usually something generic and uninformative like “Scan0001.pdf” or a combination of the date and the time like “07182015-100855.pdf”.