How to use Excel Data Model & Relationships
Have you ever been in a VLOOKUP hell?
Its what happens when you have to write a lot of vlookup formulas before you can start analyzing your data. Every day, millions of analysts and managers enter VLOOKUP hell and suffer. They connect table 1 with table 2 so that all the data needed for making that pivot report is on one place. If you are one of those, then you are going to love Excel 2013’s data model & relationships feature.
Finding Conversion ratio using Pivot Table Calculated Items
Today, lets understand how to use Calculated items feature in Pivot tables. We will use a practical problem many of us face to learn this feature – ie calculating conversion ratio from a list of sales calls.
Highlight Quarters, Weekends in pivot reports using styles [quick tip]
Here is a quick pivot table tip.
When reporting summaries by month, it would be better to highlight 3 months at a time (Jan, Feb, Mar in one color, Apr, May, Jun in another color) than showing all in one color. Today, lets learn how to do this in easiest possible way.
Show monthly values & % changes in one pivot table
Pivot tables are great help when analyzing lots of data. One of the common questions managers & analysts ask (when looking at monthly sales data for example) is,
How is the monthly performance of our teams (or regions, products etc.)?
A pivot report can answer this question in a snap. Today lets learn how to do that.
Using pivot tables to find out non performing customers
Moosa, one of our readers emailed this interesting question:
I have huge list of customers (around 1500).
Table includes following information
Customer # , Customer Name, Sales 2002, sales 2003, … sales 2012My requirements are
1. list of customer who did not have sales during all these years
2. List of customer who have not business from 2003
3. List of customer who have not business from 2004
Today, lets learn how to identify all the non-performing customers.
Sort Pivot Tables the way you want [Quick tip]
Ever looked at a Pivot table & wondered how you can sort it differently?
“If only I could show this report of monthly sales such that our best months are on top!”
Well, there is a way to do it without sacrificing 2 goats or pleasing the office Excel god. Just use custom sorting options in Pivot tables.
Displaying Text Values in Pivot Tables without VBA
Pivot tables are a great way of summarising and consolidating data to produce summary reports.
One of the main limitations of Pivot tables is that they don’t natively return Text values.
This post looks at a method to work around this without the use of VBA.