One of these four shortcut keys stops working in Windows versions of Excel:
Ctrl+9 to hide rows
Ctrl+Shift+9 to Unhide rows
Ctrl+0 to hide columns
Ctrl+Shift+0 to unhide columns
If the last one is not working for you, it is because the Windows shortcut to switch language is assigned to Ctrl+Shift. This video shows the Control Panel setting to make Ctrl+Shift+0 or Ctrl+Shift+) start unhiding columns again.

Today’s question from Bill.
Did you know that Excel’s Advanced Filter is returning partial matches?
A phrase in the criteria range is treated as Phrase*.
This is an annoying problem.

Excel Help sort of alludes to this problem and suggests a formula in the criteria range of Advanced Filter. But if you try to automate the creation of those formulas in the criteria range, it does not work.

In this video, a convoluted set of steps to automate formula creation with a carat in place of the equals sign, then Replace to change the text into a formula. This allows the Advanced Filter to work.

This Continue reading “Why Is Excel Advanced Filter Using Partial Matching 2388” →

A question from my Daytona Beach seminar last week:
We know XLOOKUP can find the number just larger or just smaller than a number. But does this also work with Text? How about with Symbols?

In this video, Bill Jelen shows how the approximate match works with XLOOKUP. Unlike MATCH or VLOOKUP, the table does not have to be sorted. You can find a value just smaller or just larger than the search value.

This also works with Text, although it is case insensitive.

And, it works with symbols, but in an unexpected sequence.

Did I know this? The Excel Status Bar Statistics pick up the number format of the active cell in the grid. This is a useful feature, and not a bug. But it could freak someone out when you get different answers depending on if you select from bottom to top or top to bottom.

Also in this episode: the four zones of Custom Number Formats.

And, the winner(s) of a free Excel PDF.

Diane has a fairly complex pivot table. She is using GETPIVOTDATA to pull data from the pivot tables to a summary report in another workbook. In another section of the report, she needs to pull YTD. But this complicates the formula, because she has to add an expanding range as the months continue.
I mentioned that she could create a second pivot table that shows the sales numbers as YTD numbers. This video shows how to copy the first pivot table and how to use Running Total In to convert from monthly sales to YTD Sales.

subtotal a column in excel
how to add subtotal function in excel
how to formulate subtotal in excel
how to subtotal sum in excel
how to use subtotal in excel for sum
using subtotals in excel to sum
how do you do a subtotal in excel
how to automatically subtotal data in excel
how to automatically subtotal rows in excel
how to calculate subtotals in excel
how to insert subtotals into excel
excel how to make subtotals
excel how to subtotal
excel how to use subtotals
how create subtotals in excel
how do i add subtotals in excel
how do i do a subtotal in excel
how do i do subtotal in excel
how do i use the Continue reading “Adding Subtotals In Excel” →