How to insert a blank column in pivot table?
We all know pivot table functionality is a powerful & useful feature. But it comes with some quirks. For example, we cant insert a blank row or column inside pivot tables.
So today let me share a few ideas on how you can insert a blank column.
But first let’s try inserting a column
Imagine you are looking at a pivot table like above.
And you want to insert a column or row. Go ahead and try it.
Use apply names to create readable formulas [quick tip]
We all know that using named ranges is a good practice. So you went ahead and created names for every value in your complex workbook. But now, what about those formulas which still refer to cells by their addresses? Here is a quick tip to make your formulas readable by replacing cell addresses with the names in one go.
Use Apply Names feature.
Formatting shortcuts for keyboard junkies
A lot of analysts swear strong allegiance to keyboard shortcuts. But when it comes to formatting a spreadsheet, these shortcuts go for a toss as formatting is a mouse-heavy activity.
But we can use a few simple & effective shortcuts to zip through various day to day formatting tasks. Let me share my favorite formatting shortcuts.
Celebrate Holi with this colorful Excel file
Today is Holi, the festival of colors in India. It is a fun festival where people smear each other with colors, water balloons, tomatoes and sometimes rotten eggs. This year we wanted to play with only water guns, but kids vetoed that idea vehemently. So we ended up driving to my sister-in-law’s place to play with colors (there were no rotten eggs or tomatoes, thankfully).
Let me smear a few colors on you
I would love to splash a jug full of color water on you and say Happy Holi. But the internets have not advanced thus far. So I am going to give you the next best option.
An Excel workbook to play holi
CP031: Invisibility Tricks – How to make things disappear in Excel?
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
In the 31st session of Chandoo.org podcast, let’s disappear.
What is in this session?
Spreadsheets are complex things. They have outputs, calculation tabs, inputs, VBA code, from controls, charts, pivot tables and occasional picture of hello kitty. But when it comes to making a workbook production ready, you may want to hide away few things so it looks tidy.
That is our topic for this podcast session.
In this podcast, you will learn
- Quick announcements first anniversary of our podcast etc.
- Hiding cells, rows, columns & sheets
- Hiding chart data points
- On/off effect with form controls, conditional formatting
- Making objects, charts, pictures disappear
- Disabling grid-lines, formula bar & headings
- Hiding things in print
CP030: Detecting fraud in data using Excel – 5 techniques for you
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
In the 30th session of Chandoo.org podcast, let’s learn how to uncover fraud in data.
What is in this session?
In the wake of hedge fund scams, accounting frauds and globalization, We, analysts are constantly second guessing every source of data. So how do you answer a simple question like, “am I being lied to?” while looking at a set of numbers your supplier has sent you.
That is our topic for this podcast session.
In this podcast, you will learn
- Quick announcements about 50 ways & 200k BRM
- Introduction to fraud detection
- 5 techniques for detecting fraud
- Benford’s law
- Auto correlation
- Discontinuity at zero
- Analysis of distribution
- Learning systems & decision trees
- Implementing these techniques in Excel
- A word of caution
Who is the most consistent seller? [BYOD]
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?