All articles in 'Pivot Tables & Charts' Category
Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?
Finding the first responded team is tricky.
Today let’s take up this problem as a case study and understand various methods to solve it. We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.Continue »
Have you ever wondered about applying different Spreadsheet Formats or Styles to reports which you may be send to different people and so the styling may be different for each recipient?
I haven’t, but in this post I will show how you can add it to your worksheets.Continue »
As announced earlier, here is the first one of our Master Classes. In this one, you will learn how to create advanced interactive charts in Excel by blending pivot tables, slicers, timelines, VBA, conditional formatting and charts. The final outcome is BEAUTIFUL!!! Monthly Master Class – Episode 1 – Trailer Check out a quick trailer of the […]Continue »
In the 54th session of Chandoo.org podcast, let’s make you awesome in Pivot Tables.
What is in this session?
In this podcast,
- Quick updates
- Top 10 pivot table tricks
- Adding same value field twice
- Tabular layouts
- GETPIVOTDATA & 2 bonus tricks
- Relationships & data model
- One slicer to rule them all
- Show only top x values
- Relative performance
- Show unique count
- Spruce up with conditional formats
- Not so ugly pivot charts
- Resources & Show notes for you
Mary Ellen, one of our readers, has an interesting conundrum,
I have some data that goes to Pivot table then to pivot chart. There is a slicer to filter the data. But when I slice, my pivot chart gets messed up. How to have the slicer, but still see the insights in the chart?!?
See above demo to understand:
This is because when you slice by a school, the pivot table gets filtered and hence % row total for that school becomes 100% (as there are no other schools).
How to fix the problem? The easy answer is to remove the slicers. But we want to have our slicers and eat a slice of them too. So we crank up the Excel awesomeness valve and get to work. There are two ways to achieve what we want.
- Old school method: Two pivot tables, some formulas & a line chart
- New Excel method: Power Pivot and a line chart
Read the rest of this article to know more.Continue »
Or more appropriately titled, the one where Power Query solves the problem in less time than it takes you to say Get & Transform Data.
Recently, one of my students Mr. K, sent me a pivot table problem.
Today my boss asked me “how much we paid to staff since the inception of our business with their respective date of joining?” He wanted to know, level wise summary of the last 16 years (on Quarterly / Year wise basis).
The records appended from the database month wise. Have a look to the file and give your ideas.
Mr. K’s data looked like above.Continue »
Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.
First see a quick demo
Looks slick, eh? Read on.Continue »
In the 47th session of Chandoo.org podcast, let’s see how Excel can make you an awesome entrepreneur.
What is in this session?
In this podcast,
- Why Excel for entrepreneurs
- Key areas of a business owner’s work
- Projects & to dos
- Customers & marketing
- Planning & strategy
- Processes & workflows
- 5 features of Excel that help
Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.
Let’s learn how to use Power Query to unpivot data.
Essentially, we are trying to go from left to right in the above picture.
Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.Continue »
Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.
How to combine the power of pivot tables with elegance of your dashboards?
The answer is: GETPIVOTDATA()Continue »
But there is one tiny problem with them. Usually we have a lot of data, but we don’t to visualize all of it. We just want to visualize latest 30 days trend or last 12 months trend or QTD or something similar. What then?
In this video, learn a powerful and very simple way to create dynamic sparklines using Excel.Continue »
Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.
Slicers – what are they?
Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.
For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.
- Add region as report filter and filter for the region you want.
- Add a slicer on region and click on the region you want.
With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.
Read more to learn all about slicersContinue »
Who is the most consistent of all?
Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.
Looking at this list, you have a few questions.
- Who is the best seller?
- Who is the most active seller?
- Who is the most consistent seller?
- Which seller has fewest invoices?
Let’s go ahead and answer these using Excel. Shall we?Continue »
Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.
Consolidating data in different shapes
We can use either VBA or Excel’s consolidation features to combine data that has same shape (ie same number & type of columns). Here is one way to do it.
But what if we need to consolidate data that is in different shapes?
Something like above.
In such cases, we can use 3 powerful tools.
- Multiple Consolidation Ranges – Pivot Tables
- Power Query
So let’s examine how to use these approaches to consolidate data in different shapes.Continue »
Howdy folks. Jeff here, bringing you a Public Service Announcement: Thanks to the magic of VBA , Structured PivotTable References are coming to a PivotTable near you! Structured References for PivotTables? So what? Well, because PivotTables are the best bit of ‘old’ Excel, and Tables are the best thing about ‘new’ Excel, and it’s about […]Continue »