Features

Spreadsheet Size in Excel for Mac

April 28, 2011 Beginner

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 [...]

Get the full story …

Total Row Formulas in Excel Tables

April 18, 2011 Features

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) [...]

Get the full story …

Excel Tables – Styles, Conditional Formatting and Data Validation

April 7, 2011 Features

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 [...]

Get the full story …

Table Design in Excel

March 31, 2011 Beginner

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 [...]

Get the full story …

Migrating to Tables in Excel

March 30, 2011 Beginner

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 [...]

Get the full story …

Fat Fingers on the Space Bar – A Conditional Formatting Example

March 25, 2011 Features

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 [...]

Get the full story …

Use Formula Auditing to Help Explain Formulas Excel

March 21, 2011 Features

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, [...]

Get the full story …

Copy Data to Another Worksheet with Advanced Filter

March 9, 2011 Features

Dear Microsoft, please fix the Advanced Filter in Excel. Copying filtered data to another worksheet should be simple but it’s not. Fixing this would help out many of your beginning and intermediate users and some of us old dogs who seemingly haven’t learned the trick. The Problem with Advanced Filter in Excel I was reading [...]

Get the full story …

Sorting with Custom Lists in Excel

March 3, 2011 Features

Every once in a while I come across a sorting problem where a column of data needs to be sorted in a manner not consistent with ascending or descending order. Thankfully, Excel allows the sort order to come from a Custom List. As an example I have some data with Date, Name, and Type as [...]

Get the full story …

Open File As Read Only in Excel

February 25, 2011 Beginner

To discourage users from making any changes to a macro file you can have the file recommend Read-Only when it’s opened. To show this pop-up box when the file opens: Choose File > Save As Select Tools > General Options (on the Save As dialog box) Check the box for Read-only recommended Click OK Name [...]