Currency format Pivot fields with one click [Friday VBA]
Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.
- Right click on any value field
- Go to Value field settings
- Click on “Number Format” button
- Choose Currency format
- Close the boxes, one after another
Unless you get paid per click, you wont be happy with all those clicks.
Wouldn’t it be cool to just click once and apply most common format to your pivot fields?
Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.
How to generate all combinations from two separate lists [Pivot Table Trick]
Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.
Here is a ridiculously simple way to do it.
A quick tip about data analysis while on bike [Video + Personal]
Wellington(NZ) sure is beautiful on a clear day. There are so many cool bike tracks, walking trails and beaches to keep you busy. Today, I went cycling on my usual route towards Makara beach. At the turn off point, I thought, “This is selfish. I can’t keep all this beauty to myself. I must share it with you.” So here we go.
A quick video about data analysis while on bike
Technically, I was not on bike when recording this video. Watch it below or on our YouTube Channel.
Analyzing 300,000 calls for help [case study]
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.
Apply Conditional Formatting using Slicers
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.
Advanced Interactive Charts using Excel [Master Class]
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 […]
CP054: Top 10 Pivot Table Tricks for YOU
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
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