Category Archives: Features

Zoom to 125 Percent – Excel 2011 Default Workbook

I want to have Excel 2011 (for Mac) open to a blank worksheet that is zoomed to 125% and it has taken a very long time to figure this out. It involves saving a template to a buried location, then manipulating the file in Finder to remove the extension. Simple, once you know the trick.

The buried location is:

    Macintosh HD:Users:username:Library:Application Support:Microsoft:Office:User Templates:My Templates:

Or you can check to see where the Excel startup files are located on your computer. Just to go Excel > Preferences then select General and click the Select… box for At startup, open all files in. This is where a modified template file should go.

Excel 2011 Preferences General

Change the Default Workbook in Excel 2011

  • Open Excel 2011
  • Choose View > Zoom, then select 125% and click OK
  • Choose File > Save As
  • In the Save As: box type in Workbook
  • Click the Format: drop-down button and choose Excel Template (.xltx)
  • Make sure you save it to the startup location (discussed above)
  • Click Save

Excel 2011 Save As Template

Note: Keep in mind that I have set Excel preferences to have my workbooks open with just one worksheet. If you have several worksheets in the Workbook Template file, you may have to set the zoom for each sheet.

The Default Workbook Secret

The trick now is to open Finder and navigate to the startup folder, select the Workbook.xltx file you just created, and delete the .xltx portion of the file name. I did this by selecting the file, clicking once to highlight the name, then selected the .xltx extension and hit the delete key.

Excel will warn you that this may be dangerous but stay the course and click Remove.

Remove .XLTX Extension

Now open Excel 2011 and you’ll be looking at a worksheet that is zoomed to 125% in the default workbook.

One More Thing – New Worksheets

We’re not done just yet because we need to do the same thing once again, but this time save the file with the name Sheet, so that when you add a new sheet to a workbook the zoom will automatically be set at 125%.

My Summary

The default files Excel uses for a new Workbook and a new Sheet are template files, with the extension removed, and located in the startup files location.

A common fallacy is that Excel for Mac uses the Normal Template. This belief took me in the wrong direction for quite some time. Hopefully this post will set the record straight.

Obviously, you can set more preferences than having a 125% zoom view of the worksheet. All you have to do is open these template files, make the changes and save (overwrite) them as template files. Then make sure the file extension is removed.

Settings and Shortcuts for Excel 2003

I recently found myself with a new, loaner laptop and a brand new copy of Excel 2003 as my default spreadsheet program. This was fine until I realized there were a number of things “missing.” Like shortcuts and settings that I’ve changed over the years to make Excel serve me, instead of the other way around.

So here’s my list of things I do to “normalize” Excel 2003.

Full Menu’s

My number one pet peeve with Excel is they “automatically customize menus and toolbars based on how often you use the commands.” But my question is, “How do new users know what menu commands are available if they’re hidden?”

I like to use “full menus” so you see ALL menu commands each time each time you click a menu. This is a standardized approach; you see the same thing each time you click a menu.

Here’s how it’s done:

    Right click the toolbar
    Click Customize…
    Click the Options tab on the Customize dialog box
    Check Always show full menus
    Click Close

Customize Dialog Box

General Options

I have only two recommendations here: maximizing the recently used file list and minimizing the number of worksheets in a new file.

General Options Settings

Recently used file list (9)

I like to set the recently used file list to the maximum number. In Excel 2003 that number is nine. This saves you time when searching for a recently used file.

Here’s how it’s done:

    Choose Tools > Options then click the General tab on the Options dialog box. Change the Recently used file list to 9 and make sure there’s a check in the check-box.

Sheets in new workbook (1)

When creating a new spreadsheet file, how many sheets do you actually use? How many times have you looked over a spreadsheet file from someone else and clicked on those bank sheets to see if they contained anything?

Remember the days when a new file had 16 worksheets as the default? Excel 2003 has just three. That’s two to many. I like to set the number of worksheets in a new file to one. If I need another, they’re easily created.

Here’s how it’s done:

    Choose Tools > Options then click the General tab on the Options dialog box. Change the Sheets in new workbookto 1.

Shortcuts on the Toolbar

These custom toolbar buttons are necessary when using Excel 2003: Paste Values, Freeze Panes, Current Region, Auto Filter, and Pivot Table. You can easily customize the toolbar to add these and more.

My Custom Toolbar Icons

For each addition to the toolbar you’ll need to access the Customize dialog box. The long way is to choose View > Toolbars > Customize… or the short way is to right-click a toolbar and select Customize… from the pop-up menu.

Once the Customize dialog box is open, select the Commands tab. Now your ready. Here’s my favorite custom toolbar commands.

Paste Values

The very first custom toolbar command icon I put up. Indispensable. Well, almost.

Here’s how it’s done:

    In the Categories pane select Edit. In the Commands pane scroll down and find Paste Values. Click and hold the left-mouse button down while dragging the icon to a toolbar.

Paste Values Command

Freeze Panes

Another indispensable toolbar command button shortcut icon for anyone who works with lots of data in proper tables.

Here’s how it’s done:

    In the Categories pane select Window and Help. Scroll down until you find Freeze Panes. Click with left mouse button, hold and drag to the toolbar.

Select Current Region

Here’s a neat button that allows you to find the shape of a data region by selecting it for you automatically. Better than the keyboard shortcut Ctrl+Shift+asterisk (*). Reminds me of the CurrentRegion property in VBA.

Here’s how it’s done:

    In the Categories pane select Edit. In the Commands pane scroll down to the bottom and find Select Current Region. Click and hold with the right-mouse button while dragging to a toolbar of your choice.

AutoFilter

A great button to save you some time when filtering tables. If the active cell is on the header row it simply turns on the filter. However, if you select a cell in the data that contains something you want to filter, clicking the AutoFilter will turn on AND filter that selection for you. A one step process that saves time.

Here’s how it’s done:

    Select Data from the Categories pane. Click the AutoFilter command with the left mouse button, hold and drag to a toolbar.

PivotTable

This command serves to initiate a PivotTable from a data table. It also brings up the PivotTable and PiotChart Wizard when you are working on an active PivotTable.

Here’s how it’s done:

Select Data from the Categories pane. Scroll down to the bottom of the Commands pane and find PivotTable and PivotChart Report icon. Left-click, hold and drag to a toolbar.

More Stuff

Find your favorite command not shown on a Toolbar and load it up. Pronto!

Where is the Insert Tab in Excel 2011?

Switching to the Mac platform finds you with certain disappointments challenges. One of these is finding no Insert Tab on the Excel 2011 Ribbon.

Windows versions of Excel (2007 and 2010) both have the Insert Tab on the Ribbon. So when you start using the new Mac version of Excel 2011, you can quickly become confused at the whereabouts of some familiar controls.

Excel 2011 has a Charts tab on the Ribbon that doesn’t exist in the Windows versions (2007, 2010). This is where the Charts and Sparklines Groups on the Insert tab are located.

Excel 2011 reminds me of a hybird version because while there’s a Ribbon, they left the old Menu bar at the top. This is reminiscent of Excel 2003 (Windows) with familiar menus for File, Edit, View, Insert, Format, Tools, Data, Window, and Help.

Generally speaking, the Insert menu in Excel 2011 holds most of the items in the Illustrations, Links, Text, and Symbols groups from the Excel 2010 Insert tab.

A Table to Help

I put together a table of each item on the Excel 2010 Insert tab, and it’s location in Excel 2011.

In the table below the two left columns show the Group and Item on the Excel 2010 Insert tab. The column on the right either shows the Ribbon location or the Menu location.

    • Data > Analysis > PivotTable is an item location on the Ribbon.
    • Insert – Photo – Picture from File… is an item location in the old Menu system.

You’ll also notice a few items that don’t exist (to my knowledge).

Excel Insert Tab Item Location Excel 2011

A quick alternative to some of these items in Excel 2011 is the Media Browser icon on the Toolbar, which gives you access to Photos, Audio, Movies, Clip Art, Symbols, and Shapes to insert into the worksheet.

Since you may want to refer to this table a little more closely here’s a PDF file to download.

Worksheet Tab Protection Icon in Excel 2011

There are times when you see something new and immediately know what it means. That’s what happened to me when I opened a file from a colleague and realized the padlock icon on the worksheet tab meant that the worksheet was protected. I was right.

Lock Icon on Worksheet Tab

Since I had opened this file in Excel 2011 (Mac) I immediately wondered if the Excel 2010 Window’s version had the same feature.

That would be a no.

It sure is nice to know that a worksheet is protected beforehand, instead of finding out by trial and error that a good number of Ribbon items are grayed out and not functional. It seems in Excel 2010 there is no real overt clue that a worksheet is protected.

Too bad. I like the intuitively obvious lock icon on the worksheet tab.

Maybe they’ll include this nice little feature when they program the next Windows version of Excel.

Excel 2010 Clipboard Group

Ribbon Home Tab Comparison – Excel 2010 Windows and 2011 Mac

I’ll review each hard-to-find item on the Ribbon’s Home Tab in Excel 2010 (Windows) and let you know where they are located in Excel 2011 (Mac). I’ve even got some nifty pictures to speed along that understanding.

Note: The Ribbons in Excel 2010 and 2007 (Windows) are interchangeable, but I’ll only refer to 2010 below.

Each Ribbon Tab in Excel is organized by Groups. In Excel 2010 the Groups are shown at the bottom of the Ribbon. In Excel 2011 the Groups appear at the top, which I prefer.

The Excel 2010 Home Tab

The Excel 2010 Home Tab has the following Groups: Clipboard, Font, Alignment, Number, Styles, Cells, and Editing.

Excel 2010 Home Tab Split

The Excel 2011 Home Tab

The Excel 2011 Home Tab has the following Groups: Edit, Font, Alignment, Number, Format, Cells, Themes.

Excel 2011 Home Tab Split

Similar Items on Both Home Tabs

The Font and Number Groups have the same items so there’s no need to review them. The Alignment and Cells Groups have the same items, and although there is some variation in the underlying drop-down options I won’t cover them in this post.

Items You Have to Hunt For in Excel 2011

I’ll review each item on the Excel 2010 Clipboard and Editing Groups, which have items located on the Edit Group and elsewhere in Excel 2011, and finish up with the Styles Group that relates to the Format Group.

Note: The Themes Group in Excel 2011 doesn’t exist on the Home Tab in Excel 2010, but can be found on the Page Layout Tab.

The Clipboard Group

From left to right, the Paste icon is the first thing you see on the Excel 2010 Home tab. It resides in the Clipboard Group. Luckily the Paste icon is located in the same position on the Excel 2011 Home tab, but in the Edit Group.

The other items in the Clipboard group — Cut, Copy, Format Painter — don’t have a counterpart in the Excel 2011 Ribbon, but rather you can find them on the Standard Toolbar.

Excel 2010 Clipboard Group

The Editing Group

The Editing Group items are a mixed bag that can be hard to find in Excel 2011 unless you add some toolbar icons to the Standard Toolbar.

Excel 2010 Editing Group

The Fill and Clear icons can be found in the Edit Group. AutoSum is located on the Formulas Tab in the Function Group. Sort & Filter exists as two separate icons in Excel 2011 and are located on the Data Tab in the Sort & Filter Group with an icon for Sort and one for Filter. The Find & Select icon is not on the Excel 2011 Ribbon but located on the right side above the toolbar in a different format: the Search in Sheet box, which has a drop-down button that will bring up options for Advanced Search and Replace.

Excel 2010 Search Box Menu

In Excel 2011 you can find Sort and Filter on the Data menu, and Find can be found on the Edit menu but all of these are less than optimal because they take you away from the Home tab and require more clicks on the mouse or taps on the trackpad than are necessary.

As you can see in the Editing Group picture above, I’ve added AutoSum, Sort, and Filter icons to the Standard Toolbar to make these functions only one click away. This is a lot easier than hunting them each time.

The Styles Group

In Excel 2010 the Styles Group mostly corresponds to the Format Group in Excel 2011.

Excel 2010 Styles Group

The Format As Table icon in Excel 2010 is located on the Tables Tab in the Table Styles Group in Excel 2011.

Summary

The first time you use the Excel 2011 Ribbon Home Tab can be a trying experience if you’ve just come over from Excel 2010 or 2007. Hopefully this little summary with pictures is a help to those of you who, like me, get frustrated with Excel for making things so different between Windows and Mac.

Create a List in Excel 2008

I was surprised to find a List Wizard in Excel 2008 (Mac) because Excel 2007 (Windows) has Tables, and had already been out for nearly a year when Excel 2008 was released.

Here I’ll walk you through the process of setting up a List in Excel 2008 but won’t go into every detail because, as you’ll soon see, there are lots of features in the List Wizard. I’ll start with the same data table from my last post.

To create a List in Excel 2008, select your existing data table and choose Insert > List…, and the List Wizard will appear.

List Wizard Step 1 of 3

Step 1 of 3 wants to know Where is the data is for your list? and has correctly selected the range and guessed that My list has headers. It also wants to know Where do you want to put the list?

I leave all of these selections unchanged and select Next > to move along to Step 2 of 3.

List Wizard Step 2 of 3

Here you can select each column in the List and change the Column name and Data Type.

List Wizard Data Type Selections

The Settings button takes you down-the-rabbit-hole, so to speak, and brings up a Column Settings dialog box.

List Wizard Column Settings dialog box

Here you can also change the Column Name and set the Data Type but you can also choose the cell Formatting, set up Conditional Formatting, and Data Validation.

Click Formatting… to bring up the Format Cells dialog box. Select the Condition Formatting… button to bring up the Conditional Formatting dialog box. Click the Validation… button to bring up the Data Validation dialog box. Having these three features available is rather amazing. (I’ll forgo the screen shots of each dialog box.)

There are three more options on the Column Settings dialog box. Formula, Default value and Unique values only. If you select Calculated Column for the Data Type, then the Formula box becomes active and you can enter a formula for that column. If you select the check box for Default Value that box becomes active and you can enter a value that will appear each time a new row is generated. Selecting the check box for Unique Values will then only allow unique values to be entered into that particular column. You get a warning when trying to enter a duplicate value.

List Wizard Unique Values

Remember, all of these features can be performed on every column of data in the List.

Clicking Next > brings you to Step 3 of 3 in the List Wizard.

List Wizard Step 3 of 3

Here you can change the List name. If you select the box for Autoformat list after editing the AutoFormat… button becomes active and will bring up a dialog box if selected.

List Wizard AutoFormat

Finally, you can also click the box to Show totals row, and Show List visuals to On or Off or leave the default setting of Auto.

Click Finish to create the List.

List in Excel 2008

Notice the floating toolbar with List controls. The bottom three on the right toggle AutoFilters, Total Row, and Visuals. The latter being the exaggerated border around the List that shows when the list is active.

Along the top row of this toolbar you can access the List Wizard, Column Settings, insert a column, or row, and access AutoFormat.

Then there’s the List drop-down menu (second row, first item) that gives you lots of options.

List toolbar List menu

The menu item Remove List Manager is used to convert the List to a range.

Finally, there is an arrow head on the right side, in the middle of the toolbar that you can click with your mouse to bring up what looks to be a navigation menu.

List toolbar navigation

When adding a List to Excel 2008 you can utilize numerous features that seem to be quite extensive.

Give Your List a Defined Name – Oh Wait, Don’t Bother

For all the List features you get in Excel 2008, the most disappointing thing is that when you give the List a Defined Name, Excel will not automatically expand the associated range when you add data to the List.

I selected the List range, chose Insert > Name > Define… and gave my List the name TableOne, then clicked OK.

Insert Defined Name for List

I entered another row of data and the Defined Name TableOne still refers to the same range, cells A4:C7.

This is surprising to me because in Excel 2003 (Windows) you can give your List a Defined Name and Excel will automatically expand the range as you add more data.

Create a List in Excel 2003

The forerunner of the modern Excel Table is know as a List Object. Excel 2003 has this feature, and I’ll simply refer to it here as a List.

To create a List in Excel 2003, select your existing data table and choose Data > List > Create List…, or use the keyboard shortcut Ctrl+L.

Create a List in Excel 2003

Create List in Excel

To Excel will identify the List range and bring up a Create List dialog box. This box gives you the option to re-select the List range and let Excel know if the List has Headers in the first row.

List Selection in Excel 2003

Once you click OK, you will notice several things about this List.

  1. There is a thick blue border around it that becomes a thin blue border if the active cell is outside the List range.
  2. There is a new row at the bottom of the List that’s used to enter new data and has an asterisk in the first column.
  3. There is a default Header row formatted in boldface and has AutoFilter turned on.
  4. There is a List toolbar showing (unless you’ve dismissed it previously).

Create list in excel

The List toolbar gives you several options from the drop-down menu when you create list in Excel. Insert or Delete rows or columns in the List, Sort the List, generate a Form to enter data into the List, Resize the List, or Convert the List to a Range.

You can Publish the List to a SharePoint site, which is something I know nothing about. There are also buttons to Create a Chart, and to Print the List.

You can also Toggle the Total Row which gives you several functions to choose from in each column of the List.

Toggle Total Row in List

These functions all utilize the SUBTOTAL function, which take into account any filtering you may have done in the List.

Give Your List a Defined Name

If you give the List a Defined Name it will automatically expand as the list grows or changes. To do this I select any cell inside the List and use the keyboard shortcut Ctrl+Shift+* to select the List. Choose Insert > Name > Define and give the List a Name then click OK.

Define name dialog box

I can use this TableOne name in the formula =ROWS(TableOne) to see how many rows are in the List, which happens to be 4 at this point.

When I add another row of data to the TableOne List the formula reflects the change, hence Excel is automatically modifying the TableOne Defined Name range. Toggling on the Total Row has no effect on the TableOne range.

List Range Defined Name

This is a nice feature if you want to utilize the imposing INDEX function to refer to a column in the List. I can create a reference to column 2 in this List by choosing Insert > Name > Define and typing a name like ColumnTwoTableOne then typing in the formula =INDEX(TableOne,0,2) in the Refers to box and clicking OK.

Spreadsheet Size in Excel for Windows

I’ve been thinking about converting my older XLS files to the newer XLSX format, but ran into a quirk that stopped me. Maybe I’m just too impatient. Here’s my story.

A Few Facts First

Excel versions 97-2003 (Windows) have a file extension of XLS and the worksheet size is 65,536 rows and 256 columns. In Excel 2007 and 2010 the default file extension is XLSX and the worksheet size is 1,048,576 rows and 16,384 columns.

An XLSX File with Only 65,536 Rows

I open Excel 2003, create the following spreadsheet, and save the file. The formulas are listed in my last post.

Excel 2003 Worksheet Size

Next I open this file in Excel 2010, which now is in Compatibility Mode because it’s an Excel 97-2003 Workbook (*.xls) file.

Compatibility Mode

It still has only 65,536 rows and 256 columns, which is only natural because it’s still in the older XLS file format.

XLS file in Excel 2010

I save this file to the newer XLSX format.

Save XLS file as XLSX file

But this worksheet still has only 65,536 rows.  I create a new worksheet in this XLSX file and it has only 65,536 rows.

Now I’m discouraged and stop trying to convert my old Excel files to the new file format. In fact, I tried several times over the past few years to do this file conversion, both in Excel 2007 and 2010, but had the same result each time.

The Quirk

What I didn’t realize because of my impatience and inattention is that right after you save an XLS file to the newer XLSX format, Excel is still in Compatibility Mode, which is clearly labeled at the top of the Excel window.

XLSX file in Compatibilitiy Mode

Hence the smaller worksheet size.

After you save an XLS file to the newer XLSX format, just close and re-open the file to get rid of Compatibility Mode. Then all the worksheets have 1,048,576 rows and 16,384 columns.

XLSX file not in Compatibility Mode

What is bizarre is the fact that I had to open an XLS file with Excel 2011 (Mac), to help me find quirk.

Spreadsheet Size in Excel for Mac

Most people think an Excel workbook with an XLS file extension has only 65,636 rows and 256 columns in the worksheet. They would be wrong. Of course, if you don’t use a Mac there’s no way for you to discover this fact.

When you open an Excel 97-2003 Workbook (*.xls) file in Excel 2007 or 2010 (Windows) it’s done in Compatibility Mode, which you can see at the top of the Excel window.

Compatibility Mode

I put together a few formulas to show the size of the worksheet, the Excel version, Operating System version, and System info.

XLS file in Excel 2010

This information is from an XLS file opened in Excel 2010 (Windows) in Compatibility Mode. The formulas I used are:

  • Rows =ROWS(A:A)
  • Columns =COLUMNS(1:1)
  • Last Cell Address =ADDRESS(Rows,Columns,4)
  • Excel Release =INFO(“RELEASE”)
  • OS Version =INFO(“OSVERSION”)
  • System =INFO(“SYSTEM”)

XLS Files in Excel for Mac

Excel for Mac versions 2008 and 2011 do not open XLS files in this “Compatibility Mode” and the row and column limitations do not exist. What this means is that when you open an XLS file in Excel 2011 or 2008 (Mac) you get the newer, larger spreadsheet size.

XLS Worksheet in Excel 2011

The information above is from the same file I opened in Excel 2010 (Windows). As you can see there are 1,048,576 rows and 16,384 columns in this XLS file.

How odd.

Excel for Mac Compatibility

I made a change to the worksheet by typing “test” into cell E4 and saved the file with no problem. Then I typed “test” into cell A65537, which is one row larger than the XLS format permits. When I tried to save the file, the following message appeared.

Compatibility Warning Excel for Mac

Which means I should save the file in the new XLSX format to keep my changes.

Excel for Mac has preferences for Compatibility. Choose Excel > Preferences > Compatibility.

Excel for Mac Compatibility Icon

However nothing will change the fact that if you put data outside the 65,536 row and 256 column limitation for XLS files, Excel will warn you to save the file in the new XLSX format.

It’s just another variation between Excel for Windows and Excel for Mac.

Total Row Formulas in Excel Tables

Excel Tables have a Total Row that can be toggled on and off from the Ribbon. Several functions are available when the Total Row is turned on.

In Excel 2010 and 2007 (Windows) select a cell inside the Table, then choose Table Tools and click the check box for Total Row. In Excel 2011 (Mac) select a cell in the Table and choose Tables, then select the check box for Total Row.

Total Row Toggle

A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.

Total Row Formulas

Average, Count, Count Numbers, Max, Min, Sum, StdDev, and Var are options on the drop-down list for each cell in the Total Row. You can even choose More Functions…, which we won’t go into here.

When you choose a function from the list, what you really get is the SUBTOTAL function, where the first argument is the function, and the second argument is the Table column data. In the picture below, 101 represents the Average function, and [Net Product] is the data. Excel inserts this formula automatically when you select Average from the drop-down list.

Total Row SUBTOTAL formula

Using SUBTOTAL in the Total Row means that when the Table is Filtered, the function will give the correct answer.

If you toggle the Total Row off, Excel remembers the function and it will appear again when you toggle the Total Row back on.

Total Row Reference in a Formula

The Total Row can be used in a formula outside the Table. Notice the Avg Hours Per Setup formula below uses #Totals to refer to the Total Row in the Table.

Totals in Formula outside Table

By using the the Setup Hours and Number Setups from the Total Row, you can Filter the Table and get the correct answer for Average Hours per Setup.

The problem occurs when the Total Row is toggled off, which leaves this formula without a reference.

Formula with Totals Row

If you recall from my last post on Tables, I used the following formula for Average Hours Per Setup =SUM(Table1[Setup Hours])/SUM(Table1[Number Setups]) which has a different flaw. It does not work when the Table is Filtered.

Create a Formula That Works

We can replace SUM in this last formula with the SUBTOTAL function =SUBTOTAL(109,Table1[Setup Hours])/SUBTOTAL(109,Table1[Number Setups]).

Subtotal Formula with Table References

Subtotal Formula Filtered

Now when we Filter the Table the calculation is correct and we don’t have to worry about having the Total Row toggled on for the formula to work.

SUBTOTAL with Windows and Mac

It’s much easier to use the SUBTOTAL Function with Windows versions of Excel (2007, 2010) because autocomplete gives you the different function numbers for the first argument.

SUBTOTAL Autocomplete list Windows

The Mac version (2011) doesn’t have autocomplete but you can access Help, which brings you to another window with some information that helps you decide which function number to use for SUBTOTAL.

SUBTOTAL Functions

Hidden values refers to entire rows that are hidden. Excel Tables use “Ignores hidden values” as function numbers when you select a function from the drop-down list in the Total Row.

Table Availability in Excel Versions

As you may have guessed  Excel 2003 (Windows) doesn’t have Tables. They have what is referred to as Lists, which is the pre-cursor to Tables. Lists don’t have any structured data.

The same is true for Excel 2008 (Mac), which only has Lists and no Tables. If you have a file with Tables and open it with an earlier version having only Lists then you get a warning.

No Tables Excel 2008

The moral of this is to upgrade, Tables are worth it.

Excel Tables – Styles, Conditional Formatting and Data Validation

In my last post I designed a table and set the cell formatting for the first row of data, which will automatically extend to all new rows of data. The great news is that when I add Conditional Formatting and Data Validation to a Table, they also extended when data is added. But first I want to add a couple of formatting changes to my data entry worksheet.

Table Styles

Changing Table Styles is similar in Excel 2010 ( Windows) and Excel 2011 (Mac) yet the Ribbon is much different. The Mac version has a default Tables tab on the Ribbon. The Windows version only shows the Table Tools tab when a Table is active. The rest of this section deals specifically with Excel 2010, but Mac users can follow along by selecting the Tables tab on the Ribbon.

I decided to change the Table Style because I’m not a fan of the header row with white text. Table Tools TabWhen you select any cell inside a Table, the Table Tools tab appears on the Ribbon. There’s a Table Styles group that shows a single row of styles in a window on the Ribbon.

Table Styles

On the right there are up and down navigation buttons that allow you to see table styles one-row-at-a-time, which is a big waste of time for me. I click the drop-down button to see an expanded window with lots of Table Styles. As you hover over each Table Style icon, the Table on your worksheet should change its appearance, giving you a preview of what your worksheet data will look like.

If you hover your mouse long enough a tool tip will appear with a Table Style reference. I chose Table Style Medium 23, which gives me black text in the header row.

Table Style Name

You can manually change the background color of the header row, which will supersede the Table formatting for any style.

Worksheet Style and Tables

Now that my Table has some nifty formatting, one way to make it “pop” is to remove the ever-present worksheet grid lines. Choose the Page Layout tab on the Ribbon, look for the Sheet Options group, and under Gridlines uncheck View. (Excel for Mac: choose Layout  and uncheck Gridlines.)

Another unique fact is that when you scroll down so that the Table header row disappears, the worksheet column letters are replaced by the Table headers.

Of course there’s no word wrap or formatting, but at least you have some idea of what data is contained in each column. I always freeze sheet panes. That way you always see the header row.

Conditional Formatting and Tables

This particular data entry Table records a shift summary for each machine. Meaning there can be mistakes when the summary data is tabulated. One safeguard is to require that Setup, Run and Down Hours equal the Total Hours for the shift. That way errors will get highlighted instead of causing problems later when a report is run.

Select a Table Column

I select the Total Hours data in the Table by moving my mouse to the top of the header cell. When the mouse icon changes to a down arrow, I click once to select the data. (Clicking twice will select the data and the header.)

Select Table Column Data

Use a Custom Formula

Again there are differences between Excel 2010 and 2011 so I’ll give instructions for both.

Excel 2010 (Windows)

I choose the Home tab on the Ribbon and click Conditional Formatting, select New Rule, then select Rule Type: Use a formula to determine which cells to format. I enter the following formula in the box Format values where this formula is true: =H5<>SUM(E5,F5,G5) then click the Format button and change the background color. Click OK for the changes to take effect.

Edit Formatting Rule dialog box

Excel 2011 (Mac)

Choose Home > Conditional Formatting > New Rule then select Classic in the drop-down box for Style. Click the second drop-down box and choose: Use a formula to determine which cells to format. Enter =H5<>SUM(E5,F5,G5) in the box for the formula. Select the drop-down list for Format with: and select custom format. Then select Fill, choose a background color and click OK.

New Formatting Rule Mac

Notice the relative nature of the cell references in the formula. This conditional formatting will now extend as the Table expands with more data.

Conditional Formatting Formula Result

Data Validation and Tables

Data Validation is a great tool to avoid data entry errors. Dates and numbers can offer different problems so I’ll show you a couple different solutions. Remember that when you add more data to a Table the Data Validation extends with each new entry.

Data Validation for Dates

I’ll add some Data Validation to the Date column because Dates are always tricky. Two common data entry errors are 1) hitting the space bar, which results in having a date, that looks like a date, but is really a text value, and 2) missing the slash between month and day, which results in a date that’s in the year 1900.

I first select Date column data, then choose the Data tab on the Ribbon, click Data Validation (of Validate on a Mac), and select Data Validation. On the Settings tab, I select Date from the drop-down list, and select greater than or equal to from the Data drop-down list, then finally in the Start Date box I type the formula =$A$5 and click OK.

Data Validation dialog box

This will ensure a Date is entered and the value is greater than the first entry. If you aren’t going to enter data chronologically and intend to enter data for previous dates, you can manually type in a Start Date or use a Named Range constant value for the earliest date value likely to be entered into the Table.

Add an Error Alert

After testing this out to see if it works I decide to enter some Error alert text so the user has some kind of idea why they’re getting the message.

Data Validation Error Alert

Selecting a Data Validation Range

Changing a Data Validation range is problematic because you need to include the entire range with any changes and quite often that comes at a later date when you don’t remember the exact range of rows and/or columns. However, you can select the Data Validation range by selecting a cell that you know has the data validation, pressing the F5 button, click Special, select Data Validation, then choose Same and click OK.

Go To Special Data Validation

Data Validation for Numbers

In this Table a zero value is not required so users don’t have to waste time typing in a meaningless number. However, an inadvertent tap on the spacebar will leave and invisible text character that can cause #VALUE! errors when used in a formula. A simple Data Validation formula will solve the problem.

Data Validation ISNUMBER

I position my mouse at the top of the Number Setups header cell, and when the mouse icon turns to a down arrow I press and hold the left mouse button while selecting all the columns to the right (Table columns D to L). I choose Data > Data Validation (Validate on a Mac) > Data Validation and select Custom in the Allow list, then type =ISNUMBER(D5) for the Formula. Finally, I select the Error Alert tab and enter a Title and Error Message then click OK.

Data Validation Error tab

Now if I happen to hit the space bar the following message appears to stop the data entry process.

Number Alert Error Data Validation

Reader Tips

I’d like to share some reader comments pertaining to this subject.

One reader comment gave me the keyboard shortcut Ctrl+Shift+L to toggle Filters on and off, which is helpful with Tables.

When fighting fat-fingers on the space bar Conditional Formatting works OK, but one reader gave me a much better solution with Data Validation and a custom formula, which I shared in this post.

Table Design in Excel

I’m want to start using Excel Tables when building my spreadsheets. But first it’s instructive for me to explain how I think about “unofficial” tables or data tables as I like to call them.

How I Think About Tables

A data table is just a range of data, or a region of data that Excel recognizes if you were to use the keyboard shortcut Ctrl+Shift+*.

There are headings in the top row, which  I refer to as column headings or data fields. Date, Shift, Machine, and Total Hours are all examples. The Date column has dates in it, and only dates. The Total Hours field has only hours data.

Each row of the data table represents one record. For instance, think about customers. A customer record would contain name, address, city, state, zip, phone number, etc.

My Initial Table Design

The data table I want to design is for a manufacturing facility. We want to record data for each machine by shift. This is common when machine logs are used to collect transactional data throughout the shift. The machine logs are summarized at the end of each shift and it’s this data we want to input into our spreadsheet or data table.

What’s not obvious here is what data fields are collected for input. Suffice to say that the reports have already been designed for key indicators of performance and waste, the calculations traced back to their lowest common denominator or required data field, and that’s what we’re going to collect.

My Table Inputs

I started the data table in row 4 to keep the top area clear for any global input fields and controls for the user. The data fields in the header row were entered and cells formatted: bold, center, word wrap, column widths. I haven’t gone with any background colors in the header row just yet because I’m waiting to see how the Table formatting will turn out.

The header row or data fields are: Date, Shift, Machine, Number Setups, Setup Hours, Run Hours, Down Hours, Total Hours, Net Product, Waste Setup, Waste Run, Gross Product.

Initial Table Design

I’ve entered the first row of data for a machine on shift one. My next task was to format the data. I used the default Date format for the US, m/d/yyyy, centered the Shift number and Number of Setups. The Hours fields are all formatted to one decimal number. And finally the Product and Waste fields have a number formatting with comma and zero decimal.

One Reason Regular Data Tables Fail

And now the moment where an unofficial Table fails miserably. When the second record gets entered the formatting is not the same. In fact check out the data in the second row. Not good. Not the same. (The Date really bothers me.)

Table Design Fail

Convert  a Data Table to an “Official” Excel Table

Since I have the formatting set in the first row I’ll delete the second row before converting to a Table. I select a cell inside the table, then choose Insert > Table.

Create Table Dialog Box

In Excel for Mac choose Tables > New > Insert Table with Headers.

Insert Table Mac

Since Excel has recognized the correct range AND knows that my table has headers, I click OK.

Initial Official Table

Then as I’ve mentioned before, the first thing I do is remove the filter headings by choosing Data > Filter. Then I reset my column widths and re-enter the second row of data.

New Data Entered Formatting Remembered

Now comes the whole point of this article. An Excel Table remembers the formatting for each data field or column of data.

There are other things that a Table “remembers” as you extend the data, but that will have to wait until next time.

 

 

Migrating to Tables in Excel

My first epiphany with Excel was realizing how powerful it could be if you put all your data into data tables. Over the years Excel began to incorporate data tables. First in Excel 2003 as “Lists” then as official “Tables” in Excel 2007. (I’m not sure about the evolution in Excel for Mac versions.)

The reason I haven’t done more than piddle around with Tables is that common Excel users aren’t typically early adopters AND when you design data input and reporting spreadsheets you always design for the least common denominator (with regard to Excel versions.)

But I’m beginning to realize that pre-Excel 2003 worksheets are now a scarce commodity and we have lots of people using Excel 2007 or 2010 so why not start designing in some real Tables for data entry?

So I’m going on quest. To find out all I can about Tables in Excel, both good and bad. Let the chips fall where they may.

Yet this can’t be done in one post. So I’ll start now and continue with a little mini-series on Tables until I’ve satisfied my curiosity or I’m comfortable using them on a regular basis.

Right now I’ll cover a couple of things about Tables, one bad, one good.

One Bad Thing About Tables

One annoying thing about Tables is the filter handles on the heading row. I mean, filtering data is good but only when I want to filter data. Having the filter handles showing all the time obscures the headings to the point that you can’t read some of them. Case on point: the Shift heading in the picture below looks to be a different word.

Table Filter Handles

So my very first act after creating a Table is to choose Data > Filter to shut it down. Too bad this isn’t something you can toggle in advanced settings.

One Good Thing About Tables

All the cool formatting options allow you to easily change the look of any Table.

Table Formatting Styles

Nuff said, until next time.

Fat Fingers on the Space Bar – A Conditional Formatting Example

I had the distinct pleasure of helping a colleague this week, because he fat-fingered the space bar whilst entering data in a spreadsheet then couldn’t figure out why there were #VALUE! errors on the Pivot Report. I used some conditional formatting to solve the problem and save the day.

But to be fair, data entry errors are pernicious and can happen to anyone. And a lot of VBA programming is written to alert users of problems with input data. The example I’ll use here isn’t quite the same as the original but will illustrate the problem of having a space character entered into a cell, then having it used in a formula to generate an error.

The Problem with the Space Character

In the spreadsheet below, hours are entered by manufacturing in different buckets to determine where time was spent during the shift. Total hours equal the sum of Setup, Run and Downtime (DT) hours. The percentage of downtime is calculated in the %DT column by taking the downtime hours (DT) and dividing by the Total hours. Zeros are not a required entry.

Fat Finger the Space Bar

My fat-fingered friend happen to bump the space bar on several of his entries and I’ve re-created that in cell D3, which looks to be empty but contains a space character. This bothersome fiend is like the invisible man, you suspect something is near but you can’t see anything. Yet the evidence, in this example at least, is right in front of us by virtue of the #VALUE! error in cell E3.

By a process of elimination we can tell from the %DT formula (=D3/A3) that cell A3 appears to be okay with the value 8.0 so that leaves cell D3, which appears to be empty. However it requires closer inspection to find the space character.

If we Edit the cell (F2 in Windows Cmd+O in Mac) we can usually tell by the cursor position that there is a space, but when cell alignment is centered this becomes problematic. We could simply hit the delete key to clear the contents of the cell, but we need to detect the presence of the space character in some manner so we can fashion a solution for alerting the user to the fat-finger snafu.

Finding the Solution

Using the  formula =LEN(D3) returns 1 and confirms something unseen is there. Using the formula =CODE(D3) returns 32, which is the numeric code for the space character. And using the formula =ISTEXT(D3) returns TRUE, which is, in essence, why the #VALUE! error is returned by the formula.

My solution is to encode the data range in the DT column with Conditional Formatting so that when the space bar is inadvertently pressed, leaving behind an invisible space character, the cell background becomes a rose color to get the attention of the user. Of course I need to inform the user of this fact, so I’ll put a comment in the column header for posterity.

Detect the Space Character with Conditional Formatting

To select the data range I activate cell D2, type D2:D10000 into the name box and press enter.

Select range with name box

Choose Home > Conditional Formatting > New Rule then select the Rule Type: Use a formula to determine which cells to format. Next type the formula =ISTEXT(D2) into the Format values where this formula is true box.

Click the Format button to bring up the Format Cells dialog box, click the Fill tab, then select a background color of your choice and click OK. The New Formatting Rule dialog box should look something like this:

Conditional Formatting for space character

When you are satisfied click the OK button for the changes to take effect.

Please notice that the formula uses the topmost cell in the range with a relative reference. This allows all cells in the range to have this conditional formatting feature.

Conditional Formatting Space Character

This solves our data entry problem with fat-fingers on the space bar.

Revisit Your Conditional Formatting

Once Conditional Formatting is in place we tend to forget all about it until there needs to be some changes to the conditional part or the range needs to be expanded. The first question that arises is “what was the range I formatted with conditional formatting?”

To locate the original range, select any cell that you know has the conditional formatting and use the keyboard shortcut Ctrl+G then click the Special button on the Go To dialog box. Now click the radio button for Conditional formats on the Go To Special dialog box then select the radio button for Same and click OK.

Go To Special dialog box

Now that the entire conditional formatting range is selected, choose Home > Conditional Formatting > Manage Rules to bring up the Conditional Formatting Rules Manager where you can change the range or edit the formatting rule or add a new rule.

Conditional Formatting Rules Manager

You can also delete the original formatting rule should you have skinny-fingers and never unintentionally tap the space bar.

Use Formula Auditing to Help Explain Formulas Excel

When you need to present a complicated spreadsheet, the Formula Auditing feature in Excel visually helps to explain formula relationships. You can easily trace formula precedents or dependents.

Think of precedents as “what comes before” or the inputs to a formula. If you need to show a formula precedent select a cell with a formula, then choose Formulas > Trace Precedents to show where the formula inputs are coming from.

Formula Auditing Trace Precedents

Think of formula dependents as the values that feed a formula. Below you see two different cells tracing dependents. Both cells have a constant values that support, or directly influence three different formulas.

To trace a dependent, select a cell then choose Formulas > Trace Dependents.

Formula Auditing Trace Dependents

By using the Formula Auditing feature in Excel, you’re sure to give a visual look and hopefully a better understanding of the relationship between formulas and values in a spreadsheet.

Related Posts Plugin for WordPress, Blogger...