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

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 the column. And it doesn’t matter if I change the first cell at the top of the column or change a cell in the middle, all rows in the column will be modified.

Using Column References in a Table Formula

Tables have a structured data format you can see when adding a formula after the Table has been created. In my example I will reenter the Gross Product formula so that it equals Net Product plus Waste Setup plus Waste Run. Instead of cell references, Excel uses column references for the Table.

Table Formula Reference for Column in This Row

The formula =[@[Net Product]]+[@[Waste Setup]]+[@[Waste Run]] was entered in row 18 of the Table and was immediately copied to all rows in the column when I pressed Enter.

Notice that each column reference in the formula is located between square brackets [ ]. The @ sign refers to “this row” and is also included in square brackets. So the formula in cell L5 for Gross Product equals the values for Net Product in row 5 plus Waste Setup in row 5 plus Waste Run in row 5.

An equivalent formula using the SUM function is =SUM(Table1[@[Net Product]:[Waste Run]]).

Sum Formula in Table

Again, when I changed the formula in row 6, it was copied to all rows in the Gross Product column.

Formulas Outside the Table Range

As you may recall, Table Names in Excel are like range names and show up in formula autocomplete lists. The structured column references also show up in formula autocomplete lists.

I want to calculate the average hours per setup in the Table so I use the formula =SUM(Table1[Setup Hours])/SUM(Table1[Number Setups]) in cell E2, which is outside the Table range. Formula autocomplete helps me build the formula by listing all the columns in the Table — after I type the Table name followed by an open square bracket [.

Formula using Table Columns

Notice the Table1 name precedes each column reference. You have to remember the first few letters of the Table name so it will show up in the formula autocomplete list. Once the Table name is entered you have to remember to type an open square bracket [ to bring up the autocomplete list for the columns.

Or you can simply use the mouse to select the columns you want in the formula and the column syntax is entered automatically.

Select Table Column Data

Using a formula that references an entire Table column will expand to include any and all data for that column.

The same thing goes if I reference the Table in a formula. If I want to know the total number of rows in  the Table, I can simply enter =ROWS(Table1) into a cell and it will always give the correct number. (Filtering the Table has no effect on this formula.)

Formulas outside the Table Range

Tables have a built in Total Row feature that we’ll cover in my next post on Tables.

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.

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.

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 the 1900 date system starts on 1 Jan, 1900 and the first day of the 1904 date system starts on 2 Jan, 1904.

Here’s an example of the dates entered with the 1904 date system. I’ve added a column that shows the underlying date serial number.

1904 Dates in Excel

To change the Date System setting choose File > Options then click the Advanced tab and scroll down to un-check the box for Use 1904 date system.

1904 Date Setting

All dates entered into the worksheet will be altered to fit the 1900 date system and give you totally different dates, which can be confusing. Notice how the dates have changed in the picture below, but the underlying date serial number is the same.

1904 Dates in 1900 Spreadsheet

To change this I wrote a simple VBA macro to convert all the date values. Simply select all the date values then run the macro.

Sub Correct1904Dates()
For Each cell In Selection
cell.Value = cell.Value + 1462
Next cell
End Sub

It occurred to me that a macro like this is not needed for this temporary measure. The trick is to use the Immediate window of the VBA Editor and write the code on one line with a colon between the lines.

Select the date values and open the VBA Editor (Alt+F11), then show the Immediate window  (Ctrl+G) and type the following code.

VBA Code to Adjust 1904 Dates

All dates have been converted and the code is no longer needed.

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.

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 involved Excel reporting package that I wrote over two years ago the documentation saved me many hours of toil and trouble.

The following is an example what I normally like to put at the beginning of each subroutine. The overall flow of what the routine is doing, any special input/output variables or noteable arguments, and subroutines or functions that are used or called within the routine.

””””””””””””””””””””””””””””””””””””
‘ This error check subroutine looks at a number of conditions that
‘ would cause the program to fail, and provides the user with an
‘ appropriate message. It is called by either the Run Report or
‘ Run Reports DT routine and uses the argument SheetName, which is
‘ the appropriate sheet name from the calling program. This routine
‘ uses the function InvalidDatesInRange, which may call the InputDates
‘ routine if true. These both use the SheetName argument. This
‘ routine also uses the function EarlyDatesInRange, which may call
‘ the EarlyDates routine if true.

‘ CHECKS IF INPUT DATA EXISTS
‘ CHECKS FOR INVALID START DATE
‘ CHECKS FOR INVALID END DATE
‘ CHECKS IF THERE ARE INVALID DATES IN THE INPUT DATA
‘ CHECKS FOR DATES BEFORE THE EARLIEST DATE SETTING
‘ CHECKS IF THERE IS ANY MATCHING DATA
‘ CHECKS FOR ANY FUTURE DATE
””””””””””””””””””””””””””””””””””””

Within the subroutine I document segments of code to allow the reader to follow the logic. I try to keep it short and only break out the pseudo code to map out large projects.

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 mean, using Excel to do the work is simpler and usually faster than writing the code yourself.

So I created a simple worksheet with dummy data and tried out the Advanced Filter feature. In the picture below my List Range and Criteria Range are on Sheet1 and I’m just about to Copy to another location: Sheet2.

Advanced Filter Copy to Another Sheet

However, when I click the OK button the following message pops up.

Advanced Filter Warning

The Trick to Copy Filtered Data to Another Worksheet

My problem was that I didn’t know the secret. You have to initiate the Advanced Filter from the worksheet you are going to copy to. Duh, stupid me. So here’s how I defeated my nemesis, the Advanced Filter.

  1. Activate sheet 2 (where the data goes)
  2. Choose Data > Advanced Filter
  3. Select the List Range on sheet 1
  4. Select the Criteria range on sheet 1
  5. Select the radio button: Copy to another location
  6. Select the Copy to range on sheet 2
  7. Click OK

Here is the resulting data copied to Sheet 2.

Advanced Filter Results

There is a short video from Debra Dalgleish on how this is done. I’m too frustrated with Microsoft to pull off a video without disdain in my voice.

The problem I’m having is that this “work around” has been required for quite some time, with no updating of the Excel software code by Microsoft. (The video link above is for Excel 2003.) And yet in the latest two versions of Excel, 2010 and 2011, we still have to do something, that seems to me to be, completely counter-intuitive and un-productive.

Advanced Filter with VBA

I turned on the macro recorder to document the code required to execute, what I’m calling a “work around.” I then copied the first macro, modified the code, and ran it again from sheet 1 and it worked just fine.

Advanced Filter VBA Code

The first macro: CopyToOtherSheet does two things. 1) Selects Sheet 2 because that is what Microsoft requires, then 2) uses the AdvancedFilter method of the Range property to FilterCopy the CriteriaRange to the CopyToRange, with no Unique records.

The second macro: FilterCopyToOtherSheets only does one thing. It does what Microsoft should have done long ago, and that’s to copy the filtered range (sheet 1) to the copy range (sheet 2) while your on sheet 1.

The only code I changed (highlighted in red) was to eliminate the selection of Sheet 2, and add Sheets(“Sheet2”). to the CopyToRange. You can run this macro from Sheet 1 and the Advanced Filter will copy the filtered data to Sheet 2 with no problems.

Of course with static ranges this is a very limited macro, but my point here is that the code change should be minimal and if Microsoft can spend several billion dollars to get Windows 7 into Nokia phones, this shouldn’t cost to much to fix.

Okay, I’m done ranting.

UPDATE

Here is the file.

Excel 2011, 2010, 2007 format.

Excel 2003 format.

Helpful Links

Excel’s Advanced Filter Help Online

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, Lunch, Snacks, which is how I get the data.

In years past I would have solved this problem by creating another column (TypeCode) with a VLOOKUP formula keying off the Type data and referencing a new table created (on Sheet 1) with the sort order I wanted, as depicted below.

Sort Data with VLOOKUP Column

TypeCode is a VLOOKUP formula value that gives me the sort order I want, but with an extra column in my data and another worksheet with the lookup table. Too much extra stuff for this little problem.

Create a Custom List

A Custom List can be created in Excel 2010 by choosing File > Options > Advanced > Edit Custom Lists. In Excel 2011 choose Excel > Preferences > Custom Lists. Click Add then type the list entries — press enter or return to separate entries — then click OK.

Create a Custom List in the Sort Dialog Box

In our example we’re going to add a Custom List from the Sort dialog box. I select a cell inside my data then choose Data > Sort to bring up the Sort dialog box. As you can see below, the first level is sorted by Date from Oldest to Newest. The second level column is Type where I select Custom List… from the Order drop-down box, which will bring up the Custom Lists dialog box.

Sort Dialog Box Custom List Order

Here I click Add, and type in my list: Breakfast, Lunch, Dinner, Snacks, Exercise then click OK.

Custom Lists dialog Box Add

You now see the new Custom List in the second level Order column of the Sort dialog box where you can now click OK to have Excel complete the sort.

Sort dialog box Order by Custom List

The data is now sorted by Date (oldest to newest) and by Type using the Custom List.

Data with Sort

Sorting with a Custom List makes things simpler when Excel doesn’t have a default sort order to fit your needs.

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 A1 type 1:05, and in cell B1 enter the formula =A1*24*60 then change the cell format to General and you’ll see the 65.

Visitor #13357:
08:57:42. I NEED IN THIS FORMAT
08:57:46. 65 MIN
You:
08:58:41. Change the formula in B1 to =A1*24*60 & ” MIN”

Visitor #13357:
09:00:21. MY TIME FORMAT IN 01:01 AND IT SHOWS 60.9999999999999 MIN
NOT 61 MIN
You:
09:03:04. Try this =TEXT(A1*24*60,”#”) & ” MIN”

Visitor #13357:
09:04:38. THANKS ITS WORKING
09:04:44. THANKS A LOT
You:
09:04:49. Glad to help.

Woopra window

The strange thing I just noticed is that some of my replies have periods at the end. In a chat session no less!

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 option works well in Windows versions of Excel – 2010 and 2007.

Not so in Excel 2011 where the only comparable option is to import data from an HTML file. And while this may work wonderful in many cases, trust me when I say that trying to import an HTML file from the Excel:Mac Help website doesn’t work well at all.

External Data Excel Comparison

Locate the Excel 2011 Keyboard Shortcuts Webpage

Contrary to the Excel 2010 [Windows] Help system that defaults to showing data from Office.com, the Excel 2011 program for Mac defaults to showing Help content from your computer. Here’s how to locate the web page address or URL.

  • Open Excel 2011
  • Choose Help > Excel Help
  • Click the Go Online button (top-right of screen)
  • Close the Excel Help screen as it will dominate all windows
  • Go to your web browser and locate the Excel Help page
  • Type Excel Keyboard Shortcuts in the search box then press enter
  • Click the link for Excel Keyboard Shortcuts

You now have the web page for Excel 2011 Keyboard Shortcuts.

Bookmark or Save a Local Copy?

Bookmarking this page in your web browser is prudent at this point. And maybe that’s sufficient for your purposes. Yet a bookmark is only good when you have online access.

I’m going to save this web page to my computer because of one simple reason: When using Help in Excel 2011 you can’t switch between Help and Excel without minimizing the Help window. By using the browser and a local copy of the web page you can view the keyboard shortcuts while offline, and switch back and forth between any program that’s open.

Save Excel 2011 Keyboard Shortcuts to Your Computer

I use the Firefox browser on my MacBook Pro, but dabble with Safari and Google Chrome, and have found some interesting differences. Not in the information, but rather the files saved to your computer.

Save with Firefox

Here are the steps I used to save this page to my hard drive with the Firefox web browser.

  • Choose File > Save Page As…
  • In the Save As dialog box, type a  name you want for the web page in the Save As box
  • Click the Save As drop-down box and select Web Page, complete
  • Select the directory you want and click Save

You will see one HTML file and a folder with support files.

Save with Chrome

Here are the steps I used to save this page to my hard drive with the Chrome web browser.

  • Choose File > Save Page As…
  • Type a name in the Save As box (I left the default name)
  • In the Where box select the location for the file
  • Choose Web Page, Complete in the Format box
  • Click Save

Again you get the HTML file and a folder with support files.

Save with Safari

Here are the steps I used to save this page to my hard drive with the Safari web browser.

  • Choose File > Save As…
  • Type a name in the Export As box (I left the default name)
  • In the Where box select the Folder where you want the file to reside
  • Click Save

There’s only one file saved, a webarchive file, and no support folder with files. Since I’m relatively new to Mac’s this was a revelation to me and immediately liked the idea of having only one file. Clean, neat, and easy to move. You don’t have to worry about any other files or folders.

Web File Location

If you’ve saved the file to your desktop your done. I saved my file to a Dropbox folder on my hard drive, which ensures that it will be saved to the cloud and synced to the hard drive of any computer I have installed Dropbox on. (Download and install Dropbox, where you get 2GB of free storage.)

Save an Alias to the Desktop

  • Open Finder and locate the webarchive (Safari) or HTML (Firefox, Chrome) file you just downloaded
  • Right click the file and select Make Alias
  • Copy the Alias file to your Desktop and rename if desired

Desktop Alias

Now you can access the Excel 2011 Keyboard Shortcuts at anytime and not worry about the Help screen getting in your way.

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, it helps to have the web address or URL on the clipboard before importing data from the web. In this example I’ll use the following steps to get the URL for Keyboard Shortcuts for Excel 2010:

  • Press the F1 key
  • Type Excel keyboard shortcuts in the search box
  • Click the link for Keyboard Shortcuts for Excel 2010
  • Right click on the topic heading then select Properties
  • Triple click the Address (URL) link then copy (Ctrl+C) to the clipboard
  • Click Cancel and close the Help window

Now we have the URL on the clipboard.

Get Data From a Web Page

Choose Data > Get External Data > From Web to bring up the New Web Query dialog box. This dialog box functions as a Web browser and can be re-sized. Clear the Address bar and paste the URL from the clipboard, then press Enter or click Go.

New Web Query dialog box

The web page above will appear in the New Web Query window. Scroll down and you’ll see a right-arrow in a yellow box at the top of each table. Click an arrow to queue any table for import into Excel.

New Web Query Tables

We want the entire page so I’ll click the right-arrow in a yellow box at the top-left corner of the web page. This will give us the entire page. Once you click the right arrow it turns to a green check in a box.

New Web Query Entire Page

Now click the Options… button then select Full HTML formatting.

Web Query Options

Since we’re importing the entire page this option will give the best formatting. Now click the Import button and Excel will ask where you want to put the data. I’m leaving the default location cell A1. Click OK.

Import Data Dialog Box

The data on the web page is imported into the worksheet. This is now an active external query.

Imported Web Page Query

To Edit the Query choose Data > Get External Data > Refresh All > Connection Properties then select the Definition tab and click Edit Query. You’re now back to the Edit Web Query dialog box where you can make modifications.

Web Page Connection Properties

To modify the data range properties, right-click any cell in the imported data range and select Data Range Properties from the pop-up box.

External Data Range Properties

The great thing about a web query is that if the web page data is updated all you have to do is Refresh the query to update the worksheet.

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 range A2:B13 for the first argument, which is what you may use if familiar with VLOOKUP. But this requires that both the second and third arguments are used (row and column position).

The MATCH function looks to cell E2, finds the exact matching value in the Month range A2:A13, and returns the row number 7 to the INDEX Function for it’s second argument. The number 2 is input for the third (column) argument of the INDEX Function. The INDEX function returns the value 1 from row 7, column 2 of the A2:B13 range.

INDEX MATCH Example 1

The second formula:

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

is different in that INDEX uses a one-column range B2:B13, so only a row number argument is required. The MATCH function looks at Jul in cell E2 and finds a match in range A2:A13, returning the number 7 for the INDEX row number argument.

A Grade Lookup Example with INDEX and MATCH

Here’s a student grade lookup formula that relies on the match type argument of the MATCH function to be 1, which finds the largest value, less than or equal to the lookup value.

INDEX MATCH Example 2

The formula in cell C2 is:

=INDEX($F$5:$F$9,MATCH(B2,$E$5:$E$9,1))

The INDEX function uses array range F5:F9 and the MATCH function array range is B2:E9. (The dollar signs ($) indicate absolute values because I copy the formula down).

The MATCH function looks to cell B2, which has the value 100, then finds an approximate match of 90 in row 5 in the range E5:E9. The 1 indicates this will be an approximate match. The INDEX function then returns the cell in row 5 of the F5:F9 range, which is the text value A.

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 an array constant.
    • If array contains only one row or column, the corresponding row_num or column_num is optional.
    • If array has more than one row and column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
  • Row_num is a row number within the array. If omitted, column_num is required.
  • Column_num is a column number within the array. If omitted, row_num is required.

If both row_num and column_num are used, INDEX returns the value in the cell at the intersection of the two within the array.

Examples of the INDEX Function

In the spreadsheet examples below, array is the range A2:B13, which is also given the named range of fQTR. The first INDEX formula in cell E4 returns the Fiscal Quarter from the array. The formula is =INDEX(A2:B13,E3,2).

The row_num argument is the Month # in cell E3, which is 5. The column_num argument is 2, and entered directly into the formula. The INDEX function looks to the array, pulls out the value from the cell intersecting the the 5th row and 2nd column, and returns the value 4.

INDEX Example 3

The second INDEX function example in cell E9 uses the named range fQTR for the array.  The formula is =INDEX(fQTR,MONTH(E8),2).

The row_number argument uses the MONTH function to pull out the number 11 from the Date in cell E8. The column_num argument is 2 and entered directly in the formula. The INDEX function returns 2 for the Fiscal Quarter, because the 11th month of the Date corresponds to the 11th row and the value in the 2nd column is 2. The value in row 11, column 2, of the fQTR array is 2.

If you wanted to get away from using 2 for the column_num argument, an alternative would be to use COLUMNS(fQTR) or COLUMNS(A2:B13), which would give you the total number of columns or last column in the array range that is 2 in this case. The formula would be =INDEX(fQTR,MONTH(E8),COLUMNS(fQTR)).

In the third example, since each value of Month corresponds to the row number, Jan =1, Feb =2, etc., the INDEX array argument only has to contain the column of data for Fiscal QTR and no column argument is required. The INDEX formula for converting a date to Fiscal QTR is =INDEX(B2:B13,MONTH(E10)).

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 relative position. Or to state another way: the MATCH function returns a position of a matched value from within a range, not the value itself.

The Syntax is:

MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value is required and what’s being matched. It can be a number, text, logical value, or reference to one of those three.
  • lookup_array is required and should be a one-column or one-row range of cells to be searched.
  • match_type is optional. The number -1, 0, or 1.
    • If zero (0), MATCH finds and exact match to lookup_value.
    • If 1 or omitted, MATCH finds the largest value that is less than or equal to lookup_value, and the lookup_array must be in ascending order.
    • If -1, MATCH finds the smallest value that is greater that or equal to lookup_value, and the lookup_array must be in descending order.

The MATCH Function with a Horizontal Lookup Array

The MATCH function in the spreadsheet below looks in cell C3 to get Thu for the lookup_value. The lookup_array is the Day names in the first row, range A1:G1. In the picture, I’m selecting 0 – Exact match for the third argument.

MATCH Function for Weekday

The MATCH function returns 5 for Weekday in cell C5 because Thu is found in the fifth column of the range.

The MATCH Function with a Vertical Lookup Array

The MATCH Function in the spreadsheet below, is looking to match Nov, in cell D2, to the Month names in range A2:A13, and finds an exact match in the eleventh row.

MATCH Function Month Vertical

The MATCH function in cell D3 returns 11, because that’s the position number of Nov in the range of month names.

I could go on, but you get the picture, right?

Related Posts Plugin for WordPress, Blogger...