By

Around here, people tend to get excited when a new version of Microsoft Office is released. Word tables that behave! New Excel formulas! More intuitively organized menus! Flash fill!

Conditional Formatting options in Excel 2013.

Conditional Formatting options in Excel 2013.

But as exciting as new features can be to a handful of people, they are generally overlooked by most users (some research states that the average corporate knowledge worker uses less than 20 percent of an application’s capabilities). Users either don’t know they exist, or don’t have the time to learn how to use them.

Discovering and learning how to use new and hard-to-find software features is part of what we do at Vitalyst. Another part is passing along that knowledge. Here are a few Excel features we think are worth learning. Your productivity levels will thank you.

Conditional Formatting

Conditional Formatting enables you to communicate more information by adding visual extras to your data. With data bars, icon sets or color scales, your data is easy to read and doesn’t lose the detail. Choose from built-in options or create custom formats.

Here’s how to create basic Conditional Formatting:

  1. Select the entire range of cells you wish to format.
  2. Click on the Home tab and click Conditional Formatting in the Styles section.
  3. Select Color Scales and choose a color pattern.
  4. Excel applies the colors to cells based on their values compared to the selected set of values.

To create custom data bars, color scales and icon sets, follow these steps:

  1. Select the entire range of cells you wish to format.
  2. Click the Home tab, click Conditional Formatting in the Styles section, and choose Manage Rules.
  3. Click Edit Rule.
  4. In this dialog, you can adjust the values and colors used by the rule.
  5. Click OK when you are done.
  6. Click OK or Apply and OK.

Browse our Tips and Tricks Library for more Conditional Formatting how-tos.

Sparklines

The Sparklines feature uses the same general principle as Conditional Formatting – adding graphics to make data easier to read at a glance. In Excel, you insert a Sparkline into a cell to view a sort of “mini chart” in the same table as the data — not outside the data as you would with a standard chart. Here’s how you do it:

  1. Click the Insert tab and choose Sparklines. You will see three choices: line, column and win/loss. Line represents the data as a line; column represents the data as column bars; and win/loss represents a positive value as a block above the axis and a negative value as a block below the axis.
  2. Select a Sparkline style.
  3. The Create Sparkline dialog box will open.
  4. In Data Range, select the cells that correspond to the range of data you wish to represent.
  5. In Location Range, select the cell or cells where you want the Sparkline to be displayed.

To modify formatting, select the cell containing a Sparkline to show the Sparkline Tools | Design contextual tab. This tab enables you to change colors, chart type, axis and more.

To remove a Sparkline from a cell, click on the Sparkline Tools | Design tab, select Clear, and Clear Created Sparkline.

Spreadsheet Inquire Add-In

This add-in, which is available with Office 2013 Professional Plus or higher, enables you to get a good look under the hood of your spreadsheet. The first step is to install it, as it is not installed by default. Here’s how:

  1. Go to File | Options | Add-ins.
  2. At the bottom of the dialog box, click the drop-down next to Manage and choose COM Add-Ins.
  3. Place a check in the box next to Inquire and hit OK.
  4. You should now see the Inquire tab in your Ribbon.

Now for a look under the hood.

Excel 2013's Inquire add-in is a powerful tool.

Excel 2013’s Inquire add-in is a powerful tool.

Workbook Analysis gives you a comprehensive look at a workbook’s logic structure and error status. The feature enables you to find and report on various aspects, such as formulas with errors, inconsistent formulas or invisible cells.

The Workbook, Worksheet and Cell Relationship options in the Diagram section will return interactive, graphical maps of your data connections, plus information about each connection such as last modified date and location.

Compare Files delivers a detailed, cell-by-cell look at the differences between two workbooks.

The Miscellaneous section includes: Clean Excess Cell Formatting, which combats “spreadsheet bloat” by removing formatting from empty cells and reducing file size; and Workbook Passwords, which securely stores passwords for files you are comparing.

In the next post, we will be covering another powerful but underused Excel feature: the pivot table. Stay tuned.

Leave a Comment

Your email address will not be published. Required fields are marked *