Contextures

Contextures
Excel news
by email

# 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.

## 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.

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.

## 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.

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.