Vinai Prakash

Vinai Prakash is the founder and master trainer of ExcelChamp.Net. Vinai has over 30 years of experience in using Excel, and is a Data Warehousing & Business Intelligence Professional. Vinai has coached and trained over 30,000 professionals in using Excel, Power BI, Python, SQL, and regularly runs Excel Data Analysis, Excel Dashboard MasterClass, Excel for Beginners, Advanced Excel MasterClass online and all over the world. Vinai's Videos on YouTube channel and his blog articles and newsletters are read by thousands of subscribers all over the world. Vinai lives in Singapore with his wife and 2 kids.

How to Replace Blanks With Any Value or Zeros in Excel

July 11, 2022February 4, 2014 by Vinai Prakash

This is a common problem. You import some text file, and half of it seems blank. The problem is that if it is showing blanks, you can’t sum the values.

To replace each blank with a zero is time consuming, boring and stupid. We want a fast method that works in just a few clicks, and saves us loads of time for our families 🙂

There are many ways to achieve this task of replacing Blank with Zeroes, or Replacing Blanks with Any Other Values in Excel.

Method 1: Use the GoTo Method to Find & Replace

Click on Home > Find & Select > GoTo Special. This will open up a new popup window.

Choose Blanks Radio Button, and Click OK.

All the Blank Cells will get highlighted. Do not worry. Simply type a Zero or any other value that you want to put in the blank cells.

Do this in the currently active cell. Press

together.

Voila! The keyed in Values is showing in all the Blank Cells now.

Method 2: Use an IF condition

For example, if you want to selectively key in 0 for blanks, key in this formula below.=IF(A2=””,0,A2)

This formula checks if the cell A2 is blank or not. IF it is blank, it will replace it with a 0, or else it will not change it.

Once done, you can drag the formula to the selected cells. Works beautifully!

Method 3: Variation of the IF Method, using the ISBLANK function

Now we have an in-built function that can check if the cell ISBLANK or not.

=IF(ISBLANK(A2),0,A2)

Same Magic!

If you have any other favorite method, do share it with us below in the comments!

If you’d like to learn more Excel Shortcuts, Tips & Tricks, here are some additional articles:

Questions About Any Feature in Excel?

If you have any questions regarding any feature in Excel, simply post a comment below and I’ll help you with it. All the best!

And Subscribe to our Weekly Excel Tips Newsletter, so we can send you more Short cuts & some Pivot Table Techniques by email. Only useful stuff, without the hype. And we won’t spam you. So don’t worry. Just subscribe!

Cheers,
Vinai Prakash, PMP, ITIL, GAP, Six Sigma
Founder of ExcelChamp.Net

Vinai conducts 1-2 day seminars and Training Sessions on Microsoft Excel all over the world, helping companies maximize their productivity in using Microsoft Excel. If you would like to find out more, simply contact us.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Dynamic Chart Titles in Excel

July 11, 2022February 3, 2014 by Vinai Prakash

A chart title in Excel is usually hard coded.

This means that when underlying data changes, the heading does not change… it remains static… unless someone notices it, and modifies it manually.

Thus, headings like “Sales for September 2014”, “Revenue for Q1, 2014” etc. are not wise, and may even reflect the wrong picture… specially if the data is updated, but the heading is not.

Most people do not realize that it is possible to customize the heading, and it could be a formula, or based on any cell in the worksheet.

How To Customize A Chart Heading?

Simple. First use formulas to build a dynamic heading in a particular cell in Excel. It could be like:

=”Sales for ” & A1

Second, Click on the Heading in the chart. Once the heading is highlighted, move to the formula bar. Either key in this formula in the formula bar, or refer to the cell which contains this formula.

Now the chart will show a Dynamic Chart Heading… that changes, based on the data selected.

Hope this helps! Let me know if you have any other challenges in Excel.

Cheers,Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

How To Get Running Totals In a Pivot Table

July 11, 2022February 2, 2014 by Vinai Prakash

Pivot Tables are great in summarizing data from thousands of rows in just seconds. You can get Sum, Count, Average of numbers from any grouping in a Pivot table.

But the one thing that you need at times is a running total in a column. For example, in January, you sold 100 cans, and in February you sold 120 cans. So the total cans you sold till Feb is 100+120=220. This is a running total up to the month of February.

If you want Excel to do this, you can write a formula in the data cells. But to get this thing done in a Pivot Table, you need to use a different way. And it is already pre-built in Excel Pivot Tables, starting from Microsoft Excel 1995.

All you need to do is to click on the Data Field in the Pivot Table Field List, and a popup comes up.

Choose the last option – Value Field Settings.

Move to the Second Tab – Show Values As.

Then select “Running Total in” in the drop-down. Select a base field. It must be a text field, like Product, Category, Region etc.

Show Values As - Running Total In — Show Values As – Running Total In

Voila, you get a running total in the data field inside the pivot table. And all you did was to select the correct option. No formulas to write… no calculations to make or check. Isn’t it wonderful?

Using % Running Total In

Similarly, if you choose % Running Total In as the calculation, the running total numbers would be displayed as a percentage of the total. Giving you a much nicer perspective.

Running total Percentage in Excel Pivot — Percentage of Running total

If you’d like to learn more Excel Pivot Table Tricks, here are some additional articles:

Questions About Pivot Tables in Excel?

If you have any questions regarding Pivot Tables in Excel, simply post a comment below and I’ll help you with it. All the best!

And Subscribe to our Excel Tips Newsletter, so we can send you more such Pivot Table Techniques by email. Only useful stuff, without the hype. And we won’t spam you. So don’t worry. Just subscribe.

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Removing Duplicate Values in Excel

July 11, 2022February 1, 2014 by Vinai Prakash

As soon as you begin working with a larger set of data, this problem of having duplicate values begins to creep in.

And it is most difficult to pick up duplicates. You can use this technique to identify duplicates in Excel. The duplicate values will be highlighted in yellow.

data with duplicates — Data Showing Duplicate Values

But you may not only want to find the duplicates… you actually want to eliminate them completely. In this case, read on, and find how to remove the duplicate values.

First Method: How to Remove Duplicate Cells in Excel

In Excel 2003 and previous versions of Excel, it was extremely frustrating to delete duplicates. You had to write a complex formula, to identify the duplicates first, and then delete them manually or through a macro or a series of steps.

Starting from Microsoft Excel 2007, and continuing in Excel 2010 & Excel 2013, there is a dedicated button on the Data tab, called Remove Duplicates.

All you need to do is to highlight the row of cells which contain the duplicates, and click on the Remove Duplicates button on the Data tab.

Excel will popup a window to ask about the column, and whether headers exist.

Once you click OK, the duplicates are removed, and you are shown another popup informing you of the duplicates removed, and the number of unique values remaining.

The Resulting data set only has unique values.

Data without Duplicates. only unique values remain — Data without Duplicates.

Do keep in mind that when a duplicate values is found, the first occurrence is considered original, and the second instance is removed. Any other occurrences are also removed. Only the first unique value would remain.

This is a quick and simple way to remove duplicates in Excel.

Second Method: Convert Data Range into a Data Table

If you convert your data into a table, you can use a lot of in-built functionality that only emerged in Excel 2007 onwards.

You can press Control + T to convert a data set into a Table.

Once the data is converted into a Table, you will see a new Tab called Table Tools appear in the Menu bar, at the end of the menu set.

Click on it, to activate the Table Tools Menu.

With the table tools menu, you have another option to Remove Duplicates, which works pretty much the same way as the first method.

The great thing is that the entire duplicate data set is removed completely. The remaining data moves up, and you don’t end up with any blank rows. Thus, no cleanup is required after the duplicate removal.

With these methods, you can remove duplicates, and you can have a clean set of data to perform further data analysis.

Let me know if you use any other methods to identify or remove duplicates.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Additional Resources:

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

How To Show Values & Percentages in Excel Pivot Tables

July 19, 2022January 30, 2014 by Vinai Prakash

Want to Be a Power Analyst?

They have a secret… The power analysts NEVER, repeat NEVER report any raw numbers directly.

They only talk in PERCENTAGES!

there were 4.3% new jobs created in the last quarter,
the economy shrank 2.45% year on year…
the ROI increased 5.34% in this month compared to the previous month, or
the customer service satisfaction index improved by 4.8% this quarter, over the industry average…

This is how analysts speak. So up your game, and speak in Percentages only from now onwards…

But where do you get all these percentages from in Excel?

To create a Percentage Table in Excel, all you need to do is to learn the simple Pivot Table techniques of Excel.

These techniques help you to show both values and percentages at the same time. It’s like having your cake and eating it too!

Pivot Table Showing Values & Percentages

Ever since I published the How to Analyze data with Pivot Tables Video on YouTube, I receive an average of 2-3 new queries every day, asking for more tips on getting more juice out of using Pivot Tables in Microsoft Excel. I have now created the ultimate guide of 10 Methods to Summarize Data in Excel.

This tip about showing Percentages in the Pivot table is pretty simple, yet it could make you into the next Power Analyst you see on CNBC or other TV News channels.

You see, analysts crunch numbers on Business Shows, News Channels all day long. And they make it look so sexy, so cool….

They talk about Year or Year Growth, Quarterly Income Growths, Dividend per share, and most importantly, the presentation has an impact on you… because they talk about percentages.

Simply learn this neat trick to Display Values & Percentages in Excel Pivot Tables.

Yes, a simple “We have 528 widgets” does not make anyone notice the numbers or their impact on the business.

But the same thing converted into “28% of our inventory is stuck in widgets” can make a big difference and make people take notice of what you say.

How to Convert Numbers Into Percentages?

Well, most of the time, this nifty trick can be done using the basic Pivot Table in Excel. And the version of Excel does not matter. You could use Excel 2010, Excel 2013, Excel 2016, Excel 2019 or even the latest Microsoft Office Excel 365. Pretty much any version of Excel will do.

Let’s say you are showing Categories, Products and their inventory in a Pivot Table like below.

It is useful to see the inventory by category, but it does not clarify things. So let’s add some clarity to our Pivot table, by adding a percentage.

Adding a Percentage Column in the Pivot Table

If you just drag the Stock field to the Values section of the Pivot Table one more time, the output Pivot Table would look like this.

after_adding_second_value_in_pivot — Pivot Table showing 2 identical sets of columns for the values

Now you click the Second Stock Field (Sum of Stock) in the Values section and click on it again. You will see a popup like below.

Choose Show Value As > % of Grand Total. In some versions of Excel, it might show as % of Total. This is fine.

Newer versions of Excel, like Excel 2016, Excel 2019 or Microsoft 365, show a % of Grand Total when you right-click on any numeric value. This is the key way to create a percentage table in Excel Pivots.

The Pivot view now changes to this:

Pivot with Values & Percentages — Pivot showing Values & Percentages both at the same time

Isn’t it magical! Now you can see that the Art category accounts for 42% Inventory, and Computer Products account for 35%, followed by Stationery, at 23%.

After converting the raw numbers into percentages, it is more insightful than looking at absolute numbers only.

And applying such nifty techniques to create a Percentage Table converts you into a Power Analyst who talks in Percentages, not just numbers.

Use this neat trick to convert numbers into percentages, and especially convert it to a % of Grant Total. It is a really useful technique.

I am sure you will love this simple technique. Hope it helps! Do share it with others on Facebook or LinkedIn.

And do comment or write to tell me of any issues you are facing in using Excel every day. There may be a simple solution that can save you a lot of time. At ExcelChamp, I solve many small problems each day to make Excel easy for everyone.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Cheers
Vinai Prakash

Need Tips on Pivot Tables, Data Analysis or Creating Better Charts?

Do check out these useful articles & Video Resources

And if you want to learn Advanced Excel fast, then check out our detailed guide.

FREE COURSE ON PIVOT TABLES TO ANALYZE DATA – No Signup is required. Click & Start watching Videos For Free and improve your Pivot Table Skills.