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 ddalgleish@contextures.com

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:

  1. 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.
  2. 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.
  3. 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.
  4. Chandoo tells us why the INDEX function is so awesome -- if you haven't used it yet, be sure to try it out!
  5. 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.

Fall Flowers

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.


Contextures Products

Save time with our "Done For You" Excel products.


Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.




Privacy Policy


Contextures Inc., Copyright 2019
All rights reserved.