Intermediate | Excel Semi-Pro | Page 2

# Intermediate

## Dynamic Table Reference with INDEX

April 20, 2011 Intermediate

Tables have structured data so you can easily use column references in formulas. But with the Table name AND column name, the reference can be rather lengthy and hard to understand at first glance. As an alternative, you can simply create a dynamic column reference in a Table by using the INDEX function in a […]

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 …

## Formulas and Structured Data in Excel Tables

April 13, 2011 Intermediate

I created a data table on a worksheet, then converted it to an Excel Table. Before the conversion, I had entered a formula for Gross Product in the last column. This formula looks like most formulas do in Excel, with cell references, as pictured below. The Gross Product formula in cell L5 is =K5+J5+I5. The […]

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 …

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

## Quickly Adjust 1904 Dates for the 1900 Date System in Excel

March 22, 2011 Intermediate

An associate of mine “helped” me out once by putting a worksheet together for data entry. After the worksheet had several days of user input, I found out the workbook was created on a Mac with an older version of Excel for Mac (2004) that defaulted to the 1904 date system. Since the worksheet in […]

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 …

## Documentation for VBA in Excel

March 16, 2011 Intermediate

A long time ago, in a far-away classroom I was seemingly forced to write copious amounts of documentation in a FORTRAN class just for a few lines of code. It didn’t help that I was just learning how to keyboard (type) and that I didn’t understand why pseudo code was also required by my nameless, […]

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 …