Search Contextures Sites

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

30 Excel Functions in 30 Days

 

 

 

 

Contextures News

Excel Weekly News from Contextures Jan. 15, 2013

Find records with unmatched amounts, get the basics of big data, and other tips, in this week's Excel news from Contextures.

 -  Debra ddalgleish@contextures.com

Track Weight and Nutrients With Excel

We're half way through January already -- those two weeks sure went quickly! If you made a New Year's resolution, are you still sticking with it?

If you need help with staying on target, you can download one of my free tracking templates. The one shown below is for weight, and there is also a tracker for nutrients.

For the links to the free templates, click here: Track Weight and Nutrients with Excel

Get Number of Records in Pivot Table Source

There isn't a built-in function that shows you the number of records that are in a pivot table's source data. However, with a few lines of code, you can create your own function to calculate this.

In the screen shot below, the User Defined Function -- GetRecords -- calculates the number of records for the pivot table in cell A3.

pivot subtotals video

To see my written instructions, and the code, click here: Count Records in Pivot Table Source

Show Unmatched Amounts with an Advanced Filter

You can use the Excel AutoFilter for quick and easy filters on a list or table. For complex filters, such as finding records where one amount does not match another, you can use Excel's Advanced Filter.

I made this short video so you can see how to set up a small criteria range, and enter a formula to check for records where the invoice amount does not match the paid amount. Then, run the Advanced Filter to see the results.

To see the details, and to download the sample file, click here: Excel Advanced Filter Criteria

Connect Tables in Excel 2013

An exciting new feature in Excel 2013 is the Data Model, which lets you connect tables, and show the results in a pivot table or in a PowerView (another new feature).

In his Bacon Bits blog, Mike Alexander gives an example of how the Data Model works. He connects three tables -- Transactions, Employees, Locations -- and builds a pivot table from the result.

pivot subtotals video

You can read Mike's article here: The New Internal Data Model in Excel 2013

What is Big Data?

Are you confused about big data, and what it actually is? In his Dynamical.Biz blog, Ani Lopez describes the basics of big data, and how it is handled. You won't be a big data expert after reading this article, but at least you'll know what some of the terms mean.

You can read the article here: Big Data? Hadoop? Sure I get it! What else?

Guardian Datasets

If you're looking for an interesting dataset to use for an Excel dashboard or data visualization, chances are good that you'll find one in the Guardian's archives.

To find a dataset for a specific topic, or to browse through the entire list, visit the Guardian Dataset Index page.

Recommended Excel Tools

They aren't free, but these Excel tools are a worthwhile investment, so please 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 the products mentioned in this newsletter, and earn a commission on the sales.

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.