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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest