In Excel 2007, there was a PivotChart Filter Pane, and you had to open that if you wanted to filter the pivot chart. Things have improved in Excel 2010, and the PivotChart Filter Pane is gone.
Desert Island Excel Files
Over the past few days, I’ve been without my desktop computer, which is on its way back to the repair shop.
Fortunately, most of my files were available on the external hard drive, and online storage, so there were no major catastrophes. Well, none that I’ve discovered yet!
Personal Macro File
Unfortunately, my personal Excel macros add-in file wasn’t backed up, so I had to work without it for a while.
You don’t realize how often you use something, until it’s missing!
Your Desert Island Excel Files
My Excel tip for you today is – Remember to back up ALL your Excel files, including the add-ins, toolbars, and other key files.
Imagine that you and your laptop will be abandoned on a desert island.
- Do you have everything installed that you’ll need for Excel survival?
- Or will you be voted off the island?
Check Your Backup System
Make sure your backup system is copying files from the C:Documents and Settings/YourName/Application Data/Microsoft folder, such as the Addins and Excel files.
Island Internet Access
And let’s hope that desert island has internet access, so you can send email to your clients, and use the files that you have stored in your online storage folders!
What are your desert island backup plans?
__________
Excel Snow Days
It snowed here all day on Wednesday, and caused traffic problems and backaches. Usually the city clears the streets very efficiently, but things were a mess on Wednesday.
Maybe some of the snow removal contracts expired in mid-March, so the full fleet wasn’t available.
Excel Totals for Top 3 Plus Other
In a pivot table, you can use the built-in Top 10 command, to show the Top 10 or Bottom 3, or almost any other top/bottom summary.
In this post, we’ll see how to show top 3, plus “other”, to show the remaining amounts.
See the Secret Excel Error Number
I’ve been working with Excel for approximately 125 years, and never realized that there was a hidden number in the worksheet error messages.
Excel Error Message
For example, if you try to insert a column in Excel, you might see this message that warns “Cannot shift objects off sheet“.
See Error Message Number
To see the specific error number for that message, you can press the following keyboard shortcut:
- Ctrl + Shift + I
The error message number appears in the bottom right corner of the error message. I’ve circled the number in the screen shot below.
Use the Error Number
That error message number could be useful if you’re doing a Google search, to find a solution to a specific problem.
Or, that number could be useful if you’re searching in the Microsoft Knowledgebase, or anywhere on the Microsoft website, for specific help.
Did you know those error numbers were hidden in the messages?
_________________
Excel Function Friday: Subtotal and Sumproduct with Filter
Last week, we used the Excel SUBTOTAL function to sum items in a filtered list, while ignoring the hidden rows. Now we’ll look at ways to use Subtotal and SumProduct with filter settings applied.
Continue reading “Excel Function Friday: Subtotal and Sumproduct with Filter”
Change Values in a Pivot Table
One of my clients uses a pivot table to summarize product sales, using sales data from their accounting system. Occasionally, they’d like to type a number in the pivot table, but Excel won’t let you change values in a pivot table. Here is a workaround for that limitation.
Change Excel Function With SUBTOTAL
We looked at the Excel SUBTOTAL function on Friday, and saw how it works with hidden rows.
- The first argument tells Excel which function you want to use, such as SUM (9), MAX (4) or AVERAGE (1).
Excel Function Friday: Sum Filtered List With SUBTOTAL
The Excel SUM function does a great job of adding numbers on a worksheet, and it’s probably the first Excel function that you learned about.
But SUM might not be the best function to use in all situations where you need a total.
Continue reading “Excel Function Friday: Sum Filtered List With SUBTOTAL”
PivotPower Add-in Update
Long ago, when many of the pivot table features were hidden away in obscure menu and dialog boxes, I created the PivotPower add-in. It makes it easy to do pivot table tasks, such as:
- change the summary function for all the data fields from Count to Sum,
- reset the field captions,
- protect the pivot table layout,
- and many other tasks
When you install PivotPower, it adds a a drop down list on the Add-ins tab of the Excel Ribbon, or a menu on the menu bar, in older versions of Excel.
Latest PivotPower Update
In the latest version of PivotPower, most of the commands will only affect the selected pivot table.
For example, if there are two pivot tables on the worksheet, select a cell in one pivot table, before using the COUNT All Data command. The data fields in the selected pivot table will change.
If the active cell is not in a pivot table, all pivot tables on the active sheet will be affected when you run the command. In the screen shot below, a cell between the pivot tables is selected.
When the SUM All Data command is selected, both pivot tables will be changed.
PivotPower Fix
This version of PivotPower also has a fix. In the previous version, if you selected the AVERAGE All Data command, the Data labels changed, and stayed as “Avg” even if you selected a different summary function.
That problem is corrected in this version.
Download the PivotPower Add-in
You can read more about the PivotPower features, and how to install the add-in, on the PivotPower page on the Contextures website.
If you use the PivotPower add-in, and notice anything that should be fixed, please let me know!
___________