Sometimes you want to turnoff decimal points if the value after point is 0. Mireya, Chandoo.org member had one such situation. She writes:
I am a complete beginner in excel, how can I keep the zeros when I am working with decimals and remove them when are not required, ie
Thanks for your kind help.
Easy way: Use General Formatting
The default cell formatting in Excel is General. When you set a cell’s formatting to General, you are telling Excel,
Don’t bother me. Just figure it out.
And being a good Samaritan, Excel shows decimal point if there is something after it, else omits it.
See the demo aside to understand this.
What if your numbers are results of a calculation?
It doesn’t matter. General formatting takes good care of the cells. It shows and hides decimal point depending on the result of your formulas.
What if you want something fancy like accounting format, but turn off decimal values
Now you are talking. The General Formatting option shows numbers as typed (or calculated). So 124578395 would look like 124578395 instead of $ 12,45,78,395.
So how do you show $1,245 and $1,246.34?
Aside: You should always show decimal points if some values have them and others don’t. The below technique is useful when data is a result of calculation. For example: In a dynamic KPI report, for certain KPIs you may want to show decimal points, and omit for others.
To show decimal point if there is something after it
Just follow below steps:
- Select the cell(s) where you want this formatting.
- Go to Conditional Formatting > New rule from home ribbon.
- Select rule type as “Use a formula…”
- Check if there is a value after decimal point using a formula like =Mod(A1,1)>0
- Click the format button
- Go to “Number” tab and Apply formatting with 2 decimal places.
- Click OK & You are done!
Now, if the cell has a decimal value, it shows, otherwise the decimal point is omitted.
Related: Conditional formatting Basics
Do you deal with such situations when formatting numbers?
Often when making reports (or dashboards), I have a cell where any data can go, based on user selection. In such cases, I use conditional formatting to define how it looks based on the data. Sometimes, I also use TEXT formula to format the data. This is more suitable when data is displayed in a text box rather than a cell.
What about you? Do you face situations like this? How often you rely on General formatting? Please share your experience and tips using comments.
More on Number formatting in Excel
Understanding how Excel formats numbers (and other values) can save you lots of time when you are designing dashboards, reports or workbooks that need to presented. Check out below articles to get few more tips.
- Introduction to number formatting in Excel and 10 tips
- Preserving leading zeros in a number using formatting
- Display decimals only if the value is less than 1
- How to hide “0” in chart axis labels?
- How to hide cell’s contents using format codes?
- Adding colors to your chart labels using custom format
- Showing Indian Currency Format in Excel [and more on this]
- Develop & understand custom number formats
- Chart Axis formatting – Part 1 & Part 2
21 Responses to “Distinct count in Excel pivot tables”
The distinct count option works well but I have found that if I have a date field and want to group by year, month, etc. that option seems to be disabled. I need to do both, distinct count and group by year/month.
Example data; sales orders with item quantities with dates.
Challenge; sum the item quantities, count the distinct orders and group by month. How do I do this?
Perhaps that's not possible due to the grouping?
@Al... When you use data model based pivots, you cannot group values manually anymore. Why not use Excel 2016's default date grouping option? In this case we have just a few dates, so Excel is not grouping them, but if you have an year's worth of data, when you make the pivot with date in the row label area, Excel automatically groups them. If you have fewer dates or want to use your own grouping, just create a table with all dates, add columns with month, week, year etc. Then connect this table (these types of tables are usually called as calendar tables) to your data on date field as a relationship. Now you can create reports by month, quarter etc easily.
Is this the only way to do it in 2013? I find it rather cumbersome to have to create another data table listing dates with the another column for MONTH() and YEAR() to be able to summarise data for senior level...
I know people find adding calendar tables cumbersome, but it is a best practice and let's you add more layers of analysis quite easily. For example, adding analysis by weekday vs. weekend or by financial quarter or YTD calculations (you would need either Power Pivot DAX or some very carefully setup pivot table value field settings)
I had absolutely no idea this was possible. Very useful, nice work!
Doesn't work for 2010 version though (or at least not my works version)
Hi ,
The post has the following in it :
These instructions work only in Excel 2016, Office 365 and Excel 2013.
when i have 2 different Pivot tables, one without the enabled “Add this data to data model” option, and the other one with it enabled.. is there anyway i can link slicers between them?
if the answer is NO,, what to do ?
Quick note, the “Add this data to data model” option is not available for the Mac version.
perhaps outside scope of this article but I have found when I attempt to create a pivot table from an external data source (connection to a sql view) the "Add this data to data model" becomes greyed out. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?
Is there a way to still add a calculated field when using distinct count?
I found I can't change the date source after tick the " add this data to the data model", can you help to adv how to change the date source in such case?
Is there a way to update the source once you have added to the data model? I receive a new spreadsheet weekly and would like to update the connection so my tables pull from the new source.
Hi Crhis, I like how you have hulk (superhero) as your avatar. Do you know that there is a superhero in Excel too? It's Power Query. You can use it to solve your problem in a simple click. Here an intro if you need some guidance.
Powerful Introduction to Power Query
A big Thank you. It worked.
Hi, have survey data that I need to analyze but the challenge is that my key fields are showing horizontally. I tried to transpose the fields using Power Query, but unfortunately the new fields are returning same values on a pivot table despite using distinct values
How I can a do a pivot table with discount conts in some columns and then generate shor report filter pages. pls it drives crazy
Hi. Why grand total pivot of distinct count is 13? shouldn't it be 67?
Great Answer! Saved me lots of time!
Thank you!!!
Worked awesome! Thanks!!
Hi Chandoo,
I am using pivot tables for distinct count and now I need to update them with new set of data. But when I update the source data, all the columns and formatting of Pivot table disappears and I need to build it from Scratch.
Is there a possibility that I can update the source data with new rows added and also retain my pivot tables?