Excel Weekly News from Contextures Sep 24, 2013
Fill empty pivot table cells with zeros + more Excel tips
In this week's Excel news, you'll see how to fill empty pivot table cells with zeros, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
Just click a button on the worksheet, to create either a pivot table, or an Excel table, from multiple files. A window opens, where you can select two or more files from a folder. All the data from those files is combined into one table or pivot table.
NOTE: The files must all have the data on the first sheet, and set up with the same column structure.
Click here to see the details, and download the sample file: Create Pivot Table or Excel Table from Multiple Files
When data is missing, a pivot table shows a blank cell, instead of a zero. You can change a pivot table setting, to fill those empty cells with a zero, or other characters, like the "N/A" showing in the screen shot below.
Click here to see the details, and to watch the video: Show Zero in Empty Pivot Table Cells
I've updated one of my sample files, which makes it very easy to filter a list in Excel. Instead of using the drop down arrow in a column heading, just click on a cell. Instantly, the column is filtered by the value in that cell. Then, to see all the items again, click on the column's heading cell.
Click here to see the details, and download the sample file: Click a Cell to Filter Excel Column
Here are a few more Excel articles that I read this week, that you might find useful:
To see the best or worst results in your data, you can use the Top 10 filter feature in a pivot table. With this filter, you can see the Top or Bottom Items by value, or see the values that make up a specific percent of the total, or that add up to a specific amount.
For written instructions, see Pivot Table Filters – Top 10. To see the steps in action, please watch this short video tutorial.
It's been cool here for a couple of weeks, and now it's officially fall. A few of the over-eager leaves have already changed colour, and the woods should be lovely in a couple of weeks. The roses in the back garden look delicate, but they are still blooming, and it's nice to see their bright flowers while almost everything else is fading.
In addition to all the free
Excel tips and tutorials, there are other Excel tools that you can
invest in. To learn more about the products listed below, click
on the links to take a look at their features, and decide if they're
right for you.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.