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
Create Pivot Table or Excel Table from Multiple Files
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
Show Zero in Empty Pivot Table Cells
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
Click a Cell to Filter Excel Column
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
More Excel Tips
Here are a few more Excel articles that I read this week, that you might find useful:
- On his Excel performance blog, Charles Williams looks at the pros and cons of using Excel worksheet templates in business departments. These templates let you insert a custom sheet into your active workbook, but can cause problems.
- Jon Peltier shows a few options for Charting Survey Results, using stacked bar charts, clusted stacked bar charts and dot plots. And in an opposite view, Jorge Camoes explains why he doesn't like bar charts.
- For a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
- Chandoo tells us why the INDEX function is so awesome -- if you haven't used it yet, be sure to try it out!
- Chris Webb takes a look at the Power BI Preview from Microsoft, and is impressed with what they have delivered. However, like most of us, he finds the purchasing options very confusing.
Video: Pivot Table Filters for Top 10
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.
Recommended Excel Tools
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.
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
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.