Search Contextures Sites
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 firstname.lastname@example.org
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.
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.
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?
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 ToolsThey 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.
- 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
- Excel Pivot Table Intro Course
Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.