So you have decided to up your game with Excel and / or Power BI this year and now ravenously looking for books to read. You have come to the right place. Here is my list of recommended best Excel books, and books on Power BI, visualization, dashboards, VBA, Macros and analytics.
Use below links to navigate the relevant section of this page:
Best Excel Books
Excel 2016 Bible by John Walkenbach
If you are new to Excel or have used it for a while, you can’t go wrong with John’s tome. This 1,100 page tome covers everything from getting started with Excel to some nifty advanced use cases. It is a good reference book to have on your desk or in the team library. Despite its size, this is an easy read.
Excel 2016 by John Walkenbach on Amazon
Microsoft Excel Data Analysis & Business Modeling by Wayne Winston
I bought Wayne’s book in 2013 (same book, for Excel 2013) to learn all about how to apply business modeling, analytics and statistical analysis techniques in Excel. This is a very practical and insightful book and a must have for anyone working in advanced analytics, reporting, modeling or optimization space.
Microsoft Excel Data Analysis & Business Modeling on Amazon
Data Smart by John Foreman
Data Smart teaches all about data science, machine learning, statistical analysis and business optimization and then shows you how to implement fancy algorigthms using Excel. I recommend this book to anyone in the space of analytics or reporting. John has excellent penmanship and wicked sense of humor. You will find it hard to put down this book. I read and re-read this book several times.
Best books on data & visualization
Storytelling with Data by Cole Nussbaumer Knaflic
This book is on my to-read list. Many of us are in the profession of story telling and communication. Working with data and coming up with effective storytelling is vital for our success. Storytelling with Data gives you that guidance and makes you a better professional.
Storytelling with Data on Amazon
Information Dashboard Design by Stephen Few
Stephen Few’s classic book on dashboard design is what helped me become an expert in making awesome dashboards. I have the previous edition of his book and refer to it every now and then when I need some inspiration or smack on the back for going with a stupid design. Read it cover to cover and implement as much as you can.
Information Dashboard Design on Amazon
Best Power BI Books
Now things get tricky. Power BI is a rapidly evolving software. So any book on it, is bound to be outdated in a month’s time. I learned all my Power BI by experimenting, reading blogs and watching YouTube videos. That said, Power BI is not one software. It is a combination of three different tools:
- Power Query to process and clean data
- Power Pivot for modeling & analyzing data
- Power BI Visuals for creating outputs
I think parts 1 & 2 of Power BI puzzle (Power Query & Power Pivot) are the hardest things to learn. This is where books can help you tremendously. Since you will be learning the technique and language more than screen features, a book on Power Query or Power Pivot tends to have longer shelf life. So keep this in mind where reading my book recommendations.
Beginning Power BI by Dan Clark
Power BI is a new & rapidly changing technology. Hence, getting a reliable book on it is hard. Someone recommended this to me and I find the topics quite relevant and useful for a new Power BI user.
Power Pivot and Power BI by Rob Collie & Avi Singh
Rob & Avi do a good job of explaining Power BI & Power Pivot for traditional Excel users. If you are coming from Excel background and want to learn Power BI, pick up this book. Get the kindle copy as this uses eTextbook format which works very well on your tablet or PC.
Power Pivot and Power BI on Amazon
Learn to write DAX by Matt Allington
DAX can be very daunting and gnarly when you are getting started. I find Matt’s book approachable and elegant. This will help you overcome your fear for DAX and make useful calculations out of your data. Use it to take your reporting to next level.
M is for (Data) Monkey by Ken Puls & Miguel Escobar
This cheekily named guide will help you navigate the murky, unexplained waters of Power Query. That said, the book is 3 years old, so it is WAY behind times when it comes to what is new & better in Power Query. Microsoft has rapidly changed what & how of Power Query in the last 18 months and some of the techniques in the book feel outdated. Nevertheless, a good book to have. Get the kindle version, that way if Ken decides to upgrade the book, you will have new content for free.
M is for (Data) Monkey on Amazon
The Definitive Guide to DAX by Marco Russo & Alberto Ferrari
The Italians book is gold standard when it comes to writing ninja level DAX. Unfortunately, this book is not what you need if you are a beginner. Start with either Matt’s book or Rob’s book and graduate to this one. It will help you understand inner workings of DAX and make it your genie to pull of awesome magic from your data.
The Definitive Guide to DAX on Amazon
Best Excel VBA & Macros books
Excel VBA Programming for Dummies by John Walkenbach
John’s book is the must have if you want to learn VBA programming in a comprehensive, clear way. It comes with many practical exercises, tips & best practices. I have the 2010 version of this and it helped me immensely when I was learning VBA programming.
Excel VBA Programming for Dummies on Amazon
Excel 2016 Power programming by Mike Alexander & Richard Kusleika
Mike & Dick are known for their generosity and creativity in Excel space, naturally this become reflects that. It is a treasure trove of good tips, examples and code base.
Excel 2016 Power programming on Amazon
My *own* books
Hey, I am an author too. I like to toot my horn once in a while. So if you are looking for books to polish your skills, consider these two.
PS: I am in the middle of writing my first Power BI book. This will be a beginner’s guide to everything Power BI. Watch out for an announcement about this.
The VLOOKUP Book by Chandoo
Learn everything from simple vlookup to complex INDEX, MATCH combinations in one book. Don’t be fooled by the name. This one goes beyond VLOOKUP and teaches you many powerful formula techniques when it comes to looking up data.
Excel Dashboards Book by Chandoo & Jordan Goldmeir
Create powerful, insightful and effective business dashboards using this handy guide. Learn all about charting best practices, VBA driven automation and more in this book. Also learn how Power Query and Power Pivot can revolutionize your workbook’s calculation engine.
Excel Dashboards Book on Amazon
What is your favorite Excel or Power BI Book?
Of course this list won’t be complete without your recommendation. So tell me dear reader, what is your favorite Excel or Power BI book? Post your answers in the comments section.
Note about the links in this page:
I am using my Amazon affiliate links in this post when recommending the books. That means, if you click on the links and make a purchase, I will receive a small commission from Amazon. This has no impact on my recommendation. I have personally read most of the books and recommend them even if there is no commission involved.
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.