Remove duplicates & sort a list using Pivot Tables
Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it. It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8). But today I want to show you a technique I have been using to dynamically extract and sort […]
How I Analyze Excel School Sales using Pivot Tables [video]
Some of you know that I run an online excel training program called Excel School. If you want to join, click here. Only 8 days left.
I run excel school mainly to meet new students, understand their problems and learn new ways to solve them. But, Excel School also presents me with an interesting analytics challenges. In this post, I will share 2 pivot table based analytic techniques I used just yesterday to answer few questions I had about Excel School sign-ups.
Watch this 15 min. video to see how I analyzed the data
Data Tables & Monte Carlo Simulations in Excel – A Comprehensive Guide
If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape!
How often have you thought of or been asked “I’d like to know what our profit would be for a number of values of an input variable” or “Can I have a graph of Profit vs Cost”
This post is going to detail the use of the Data Table function within Excel, which can help you answer that question and then so so much more.
Grouping Dates in Pivot Tables
Do you know you can group dates in pivot tables to show the report by week, month or quarter? I have learned this trick while doing analysis on a pivot table today. In this online lesson on pivot tables, I will teach you how to group dates in pivot tables to analyze the data by month, week, quarter or hour of day.
Chart this Sales Data and get an iPod Touch [Visualization Challenge #2]
Here is a challenge many people face. How to make a chart visualizing sales data with several dimensions like product, brand, region, sales person name, year (or month or quarter) and one or two values like sales, # of units sold, profits, # of new customers.
In visualization challenge #2, all you have to do is a make a chart or dashboard to visualize this sales data effectively.
Product Recommendation – Excel Lookup Toolbox
Anyone working on the data using excel will know the importance of lookup formulas. They are vital for making almost any spreadsheet or dashboard. That is why when my friend John Franco, who maintains Excel-Spreadsheet-Authors.com, wrote to me about his new book Excel lookup toolbox I was truly excited. In this post I am going to share my review of this product.
Create a number sequence for each change in a column in excel [Quick Tip]
Here is a quick formula trick you can use to generate sequence numbers that only increment when there is a change. Assuming the sequence of values are in column C from C3, you can write the following formula in B4 onwards (B3 will be 1, wake up…) =IF(C4=C3,B3,B3+1) Now just copy paste the formula over […]