Excel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you dont even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to learn some cool conditional formatting tricks.
So you got your coffee mug? well, lets start!
The 5 tricks we are going to learn are,
1. Highlighting alternative rows / columns in tables
2. No-nonsense project plans / gantt charts
3. Extreme Incell graphs
4. Highlight mistakes, errors, omissions, repetitions
5. Create intuitive dashboards
If you are new to Excel Conditional Formatting, please read the Conditional Formatting Basics article before proceeding.
I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star
1. Highlighting alternative rows / columns in tables:
Often when you present data in a large table it looks monotonous and is difficult to read. This is because your eyes start interpreting the data as grid instead of some important numbers. To break this you try highlighting or changing the background color of alternative rows / columns. But how would you do this if you have rather large table and it keeps changing. The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).
- First select data part of the table you want to format.
- Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
- Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
- Now, if you want to highlight alternative rows, the formula can go something like this,
=MOD(ROW(),2)=0
which means, whenever row() of the current cell is even, to change the coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
What if you want to change background color of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.
- Set the format as you like, in my case I have used yellow color. When you are done, the dialog should look something like this:
- Click OK.
- Congratulations, you have mastered a conditional formatting trick now 🙂
2. Creating a quick project plan / gantt chart using conditional formatting:
Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.
- First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…
- Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,
=IF(AND(F$8>=$D9, F$8<=$E9),"1","")
Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell.
- Next, whenever the cell value is 1, we will just fill the cell with a favorite color and change the font to same color, so that we dont see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. This will be done by conditional formatting like below:
- Congratulations, you have mastered the art of creating excel gantt charts now
3. Extreme In-cell Graphs:
Incell graphing is a nifty trick that basically uses REPT() function (used to repeat a string, character given number of times) to generate bar-charts with in a cell. You can apply conditional formatting on top of them to give the charts a good effect. Here is a sample:
The above is a table of visits to Chandoo.org 😉 in the month of January 2008. As you can see I have highlighted (by changing the font color to red and making it bold) for the cells that have more than average number of visits in the month. I am not going to tell you how to do it, it is your home work 🙂
4. Highlight mistakes / errors / omissions / repetitions using conditional formatting:
Often we will do highly monotonous job like typing data in a sheet. Since the work is monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided by conditional formatting. I use this trick whenever I am typing something or pasting a formula over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts, could run in to thousands of rows across multiple states /regions etc.).
Lets see how you can highlight a cell when it has an error:
- First select the cells that you want to search for errors
- Next go to menu > format > conditional formatting and mention the formula as:
=iserror()(see below)
- In the same way you track repetitions, a simple
countif()would do the magic for you, or Omissions (again a countif()) - Thats it, you have learned how to save tons of time by letting excel do the job for you. Sit back and sip that coffee before it gets cold.
5. Creating dash boards using excel conditional formatting:
As I said before you can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here,
Here is how you can do it:
- Copy your data table to a new table.
- Empty the data part and replace it with formula that can go like this (I am using the above table format to write these formulas, may change for your data)
=ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol â–² (go to menu > insert > symbols and look for the above one) etc.
- Next, conditionally change the color of cell to red / green / blue or pink (if you want 😉 ) and you are done
- Show it to your boss, bask in the glory 🙂
I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star.
More DIY Excel Training:
» Excel for Beginners – Tutorials
» Excel Formula Examples
» Pivot Tables
» Project Management using Excel
» Excel Dashboards
» Advanced Excel
23 Responses to “Displaying Text Values in Pivot Tables without VBA”
Its possible to display up to 4 text values.
Have a look at the screen shot of an example that I had posted way back at the EHA and figure out how its done !
http://tinypic.com/r/muzywk/6
With Excel 2010 you can use Conditional Formatting to apply custom number formats which can display text. (In older versions you can only modify text color and cell background color, but not number formats.) Using CF allows for an even larger number of different display values.
[...] Display text values in Pivot Tables without VBA [...]
Hey,
Thanks, this helps. But how do you do it for multiple values where there is a huge amount of non repeating text?
@Soumya
The only way to do more than 4 values is to make the Pivot Table manually with formulas, of course then it isn't a Pivot table
You can of course do it with VBA
You may want to have a look at this description of how to do it here: http://www.clearlyandsimply.com/clearly_and_simply/2011/06/emulate-excel-pivot-tables-with-texts-in-the-value-area-using-vba.html
@Soumya
The only way to do more than 4 values is to make the Pivot Table manually with formulas, of course then it isn’t a Pivot table
You can of course do it with VBA
You may want to have a look at this description of how to do it here: http://www.clearlyandsimply.com/clearly_and_simply/2011/06/emulate-excel-pivot-tables-with-texts-in-the-value-area-using-vba.html
[...] Pivot Tables take tables of data and allow the user to summarise and consolidate the data at the same time. This is a great and very fast method of analysis but is restricted to handling mathematical functions on the value field resulting in numerical summaries. – read more [...]
[…] Read more here: Displaying Text Values in Pivot Tables without VBA […]
There is a very good way actually for handling text inside values area.
First you create a special column on the very left side and call it ID, and put unique ID (numbers only), and then create a pivot table with:
Row Labels and Column labels as you like, and in the Values labels use the unique ID number.
Move the unique ID number (copy paste) somewhere to the right and use vlookup to load the data you need using the ID as reference.
It is a bit longer way but for me it works perfectly to combine values as you like in any moment.
hope helps.
Regards,
Jon
Thank you! I finally understand pivot tables thanks to your clear, concise explanations and examples.
Good Day. This is exactly what i have been looking for. However when i try it on my pivot table or even when i try to recreate this exercise using the sample worksheet, i get this error:
"Microsoft Excel cannot use the number format you typed. Try using one of the built-in number formats."
Same thing here, Excel quite did not like the format in my PowerPivot. Any clues as to what may be going on? Thanks.
I have the same thing happening on my end. I'm running a normal pivot table on a .xlsm file.
@Danzi
What format did you use?
can you post the file ?
pls. help in table there is name, pan. amount. i have to make pivot table for example
NAME PAN AMOUNT
MR.X AAAAC1254T 500.00
MR.Y AAABR1258C
MR.A CFVDE2458T
MR.Z AAVCR12548C
MR.X AAAAC1254T
MR.Z AADCD245T
pls. help in table there is name, pan. amount. i have to make pivot table for example
NAME PAN AMOUNT
MR.X AAAAC1254T 500.00
MR.Y AAABR1258C 1000
MR.A CFVDE2458T 2000
MR.Z AAVCR12548C 5451
MR.X AAAAC1254T 45564
MR.Z AADCD245T 4500
how to get pivot tabe so i get PAN no. against Name.
I found an easy way to get text values in pivot table.
I create an other worksheet in wich each cell has a formula that copy the pivot table. The trick is that the formula does a lookup for the numbers in the pivot table.
The formula looks like that:
=IF(ISNUMBER(table!A1);VLOOKUP(table!A1;Code!$A$1:$B$65;2);IF(ISBLANK(table!A1);" ";table!A1))
Code is a worksheet where there is a liste of text /numbers correspondance.
As a bonus The new sheet is easier to format
Additional trick:
In my case, i encoded differents codeid with a power(2, codeId-1) so that summing then is equivalent to concatenate them.
1-A
2-B
4-C
8-D
yields :
5 - AC
14 - BCD
Hi
I want to ask if pivot can display dates in pivot field. As in a column i have customers and in row different items i want to know there last purchase date. anyone help in this??
Hello Guys, Need your help
I am doing some analysis of the cycle time of the product i.e how much time a product takes from manufacturing to the central warehouse.
I have batch numbers for the product and against them i have to pull out the diff. dates
Like the base date is from where the manufacturing start. So i have the batch number,against it's manuf. date. Now i have to pull out the date when it was quality released.
I have the quality released data but the data have duplicates, like i will have two dates or may be three for the same batch. So my main objective is to pull out the date which is latest among them.
BATCH NO. DATE of Mfg. DATE of Quality release
A1 12/4/2014 (HERE I HAVE TO PULL value)
Next Sheet
BATCH NO. DATE of Quality Release
A1 14/5/2014
a2 23/5/2016
A1 12/5/2014
A1 13/6/2014
From this sheet i have to pull up the latest date format of date here is dd/mm/yyy
TIA
[…] needed to present text instead of counts in a pivot table value column. Here is an excellent resource for Excel manipulation, in addition to an overview of pivot […]
This is great thank you.
Wow!!! Excellent!! It helped me a lot.
I am developing training tracking sheet for 200 employees with training completed date. Each employee will be attending 25 courses. How to indicate actual dates in pivot table value field.