Category Archives: Intermediate

Intermediate level information

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

Formulas and Structured Data in Excel Tables

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.

Formula Before Table

The great thing about converting to a Table is that when I add more data to the table, the formula is automatically copied down for me.

If I change this formula, then Excel will automatically copy the change to all rows in … 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

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

Quickly Adjust 1904 Dates for the 1900 Date System in Excel

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 question would be used entirely by Windows users, I decided to change to the 1900 date system to avoid any problems. Little did I know that by changing that simple date system setting would change the dates by 1462 days.

That’s because first day of … 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

Documentation for VBA in Excel

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, faceless teacher. Yes the experience was so ardous that I can’t even recall the name of the man I should be thanking right about now.

Oh to be sure, I still don’t like documenting VBA code in Excel. Yet after returning to a lengthy and … Read the rest

Copy Data to Another Worksheet with Advanced Filter

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 a post on the Contextures Blog, which indicated that you could use the Advanced Filter feature in Excel to copy filtered data to another worksheet. This feature has the potential to shorten some of my VBA code that essentially does the same thing. I … Read the rest

Sorting with Custom Lists in Excel

Custom List SortEvery 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 column headings, where the Type data can be one of five values: Breakfast, Lunch, Dinner, Snacks, Exercise. I want the data sorted by Date, in ascending order, then by Type, in the order listed above, not in ascending order: Breakfast, Dinner, Exercise, … Read the rest

A Woopra Chat About Excel

Woopra Desktop AppI use the Woopra desktop app to view live visitors to this WordPress blog. Visitors will see a Woopra window lurking about when I’m logged into my (free) Woopra account. One option is to initiate a chat session with me.

My first chat happened the other day. The chat notice startled me, but I quickly recovered and was able to interact with a visitor.

(Bold and color are my additions for clarity)

Visitor #13357:
08:53:19. HI
You:
08:53:28. Howdy

Visitor #13357:
08:54:22. I WANT TO CONVERT 01:05 = 65 MIN WHAT I DO
08:55:42. IN EXCEL
You:
08:56:58. In cell … Read the rest

Excel 2011 Keyboard Shortcuts on Your Desktop

My frustration level is high with the learning curve associated with keyboard shortcuts in Excel 2011 so I decided to import them into a spreadsheet and found out that’s not happening. Since there’s more than one way to skin a cat (sorry cat lovers, just an expression) I decided to import them from the Office:Mac website to have at my beck and call whenever the need arises.

Different Methods of Getting External Data – Windows vs Mac

Excel 2010 has a From Web option to import data from a web page. As shown in my last blog post, this … Read the rest

Import Data From the Web in Excel

Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t.

An integral part of working with Excel is using keyboard shortcuts. They make your life so much easier (in the Windows versions at least, in the Mac version I think they tend to shorten your life span).

In my last post I dealt with getting a Help Topic URL, here I’m going to use the web page Keyboard shortcuts in Excel 2010 and import to a spreadsheet.

Get a Help Topic Web Page Address

As you will see, … Read the rest

INDEX and MATCH Functions Together Again in Excel

The INDEX and MATCH functions are used together to perform lookups. The INDEX functions grabs a cell from an array, and the MATCH function contributes the row and/or column position.

INDEX and MATCH Example

Familiarity with the VLOOKUP function can cause you to restrict INDEX and MATCH to less than they can be. The example spreadsheet below bears this out.

The formula for Fiscal QTR takes the Month in cell E2 as input, finds that value in the Month column, then returns the Fiscal QTR in the same row. The first formula is:

=INDEX(A2:B13,MATCH(E2,A2:A13,0),2)

This INDEX formula uses the array … Read the rest

The INDEX Function in Excel

The INDEX and MATCH functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used INDEX and MATCH to perform a lookup it can get confusing.

I’m going to cover the INDEX function here so you can learn it well and don’t have to think about it too much when we put it together with the MATCH function.

The INDEX Function Details

The INDEX function returns a cell value from a range, given a row and/or column position number. The syntax is:

INDEX(array, row_num, [column_num])

  • Array is required and is a range of cells or
… Read the rest

The MATCH Function in Excel

The MATCH and INDEX functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used MATCH and INDEX to perform a lookup it can be confusing at first.

I’m going to cover the MATCH function (Excel) here so you can learn it well and don’t have to think about it too much when we put it together with the INDEX function. First comes the dirty details and boring syntax, followed by a couple of examples.

The MATCH Function (Excel) Details

The MATCH function searches for an item in a range of cells and returns its … Read the rest