This article is written by Myles Arnott from Excel Audit
I briefly covered Excel’s Go To Special function in the Managing Spreadsheet Risk series of articles and both Chandoo and I felt that it deserved a post all of its own.
What is Go To Special?
Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. Once you get to grips with this function and what it can be used for you will wonder how you ever lived without it.
Where do I find Go To Special?
Shortcut: F5 or CTRL + G and then click on Special…
2003: Edit > Go To
2007 & 2010:Home > Find & Select > Go To Special on the Ribbon
(Note: a cut down selection of the most useful options in Go To Special can be selected directly under Find & Select on the Ribbon in 2007 & 2010.)
Lets look at Go To Special in action
Firstly download this workbook. This is more or less the same workbook that we used in the Managing Spreadsheet Risk series, modified slightly to allow us to cover all elements of the Go To Special function. (Note that it therefore includes a lot of errors)
Here are the options on the Go To Special dialogue box:
Lets run through each of the Go to special options.
Comments
Action: Selects all cells with comments
Benefit: A quick way of finding all cells with comments, particularly useful if you want to clear all comments from your worksheet
Constants
Action: Selects all cells containing constants
Options:
Numbers: Selects all cells with constants that are numbers
Text: Selects all cells with constants that are text
Logicals: Selects all cells with constants that are logicals (TRUE or FALSE)
Benefit: The number constants in your spreadsheet should all be inputs. Highlighting all constants is a great way of checking the structure of your spreadsheet. I normally format inputs with a white background and blue font.
A great tool for auditing – select all constants and change the fill colour. This instantly gives you visibility of your model inputs and flags any inconsistencies.
Formulas
Action: Selects all cells containing formulas
Options:
Numbers:Selects all cells with formulas that return numbers
Text: Selects all cells with formulas that return text
Logicals: Selects all cells with formulas that return logicals (TRUE or FALSE)
Benefit: Highlighting all of the formulas within your spreadsheet is a great way of checking the structure and consistency of your spreadsheet.
Blanks
Action: Selects all blank cells
Benefit: A quick way to select all blank cells. This is useful if you want to quickly format all blank cells or as a way of identifying cells that look blank but actually contain a constant or formula (i.e. with white on white formatting).
(Related: Fill Blank Cells )
Current region
Action: Selects the current region
Comment: I would recommend using the shortcut CTRL + * instead
Current array
Action: Selects the entire array if the active cell is within an array
Comment: I have never used this option but would be very interested to hear if anyone has.
Objects
Action: Selects all objects (shapes, images, charts etc)
Benefit: A simple way to select all objects. This could be useful if you wanted to quickly delete all objects in the worksheet.
Row differences
Action:
Single row: Selects the cells that are different from the active cell within the selected row
Multiple rows: The comparison is made for each row independently. The cell used for comparison for each row is the cell in the same column as the active cell.
Benefit: This is a very useful auditing tool for highlighting inconsistent formulas in a row.
It also offers a quick and easy way to spot differences across multiple rows.
(Note: You can change the active cell within a selected row by pressing enter)
Column differences
Action:
Single column: Selects the cells that are different from the active cell within the selected column
Multiple columns: The comparison is made for each column independently. The cell used for comparison for each column is the cell in the same row as the active cell.
Benefit: This is a very useful auditing tool for highlighting inconsistent formulas in a column. It also offers a quick and easy way to spot differences across multiple columns.
Precedents
Action: Selects the cells that feed into the selected cell(s)
Options:
Direct only: First level precedent only
All levels: All levels of cell precedents
Benefit: Provides an alternative to Trace Precedents in the formula auditing bar. Personally I prefer using this tool to select and then colour-fill the precedent cells as it allows you to select the precedents for a range of cells rather than just one. I also find that the arrows in Trace Precedents can get a little messy.
Dependents
Action: Selects the cells that the selected cell(s) feed into
Options:
Direct only: First level dependents only
All levels: All levels of cell dependents
Benefit: As above this provides an alternative to Trace Dependents in the formula auditing bar.
Last cell
Action: Selects the last used cell within your worksheet (containing data or formatting)
Benefit: A quick way to locate your last cell. This is a very effective way of identifying the range of cells used of the worksheet.
If your simple spreadsheet suddenly becomes very large in MB terms this can be due to Excel incorrectly thinking that you are using a lot more of the cells than you actually are . A good indicator of this is that the right hand scroll bar slider becomes very small. Using Go To Special Last cell lets you quickly identify the last cell Excel thinks you are using.
Visible cells only
Action: Selects cells that are not hidden (& therefore are visible)
Benefit: Useful if you only want to change the non-hidden cells and leave the hidden cells unchanged
Conditional formats
Action: Selects all of the cells with conditional formatting applied
Options:
All: Selects all cells with conditional formatting applied
Same: Selects all cells that have the same conditional formatting as is applied to the active cell
Benefit: An easy way to quickly identify all of the cells with conditional formatting applied to them. A useful tool for understanding the formatting applied to a spreadsheet.
You need to be aware that, depending on the conditional formatting set, you may not be able to highlight the cells using a fill colour as the conditional formatting may override it.
Comment: The manage rules option within the conditional formatting menu also enables you to identify cells with conditional formatting applied.
Data validation
Action: Selects all of the cells with data validation applied
Options:
All: Selects all cells with data validation applied
Same: Selects all cells that have the same data validation as is applied to the active cell
Benefit: An easy way to quickly identify all of the cells with data validation applied to them. This is particularly useful from an auditing perspective or if you want to clear the validations in these cells.
Some considerations for Go To Special
- Go To Special only selects cells in the current worksheet rather than the whole workbook.
- Go To Special searches within the selected range, if you want to select the entire worksheet ensure that only one cell is selected
Putting this in to practice
In order to give you some examples of how to use the Go To Special tools covered above I have put together a list of actions for you to run over the attached spreadsheet. Have a play and see what you discover:
(note that the action “Select cell A1” is simply to clear the current range selected. Failing to do this will restrict the new search to the currently selected range)
1) Look for cells containing data validation and conditional formatting
Select cell A1, Go To Special, Data validation (All)
Select cell A1, Go To Special, Conditional formatting (All)
2) Check the structure of the spreadsheet
Select cell A1, Go To Special, Constants ,text, fill the selection in brown
Select cell A1, Go To Special, Constants ,numbers, fill the selection in blue
Select cell A1, Go To Special, Constants ,errors, fill the selection in purple
Select cell A1, Go To Special, Formulas (leave all options ticked), fill the selection in green
Select cell A1, Go To Special, Formulas, errors, fill the selection in red
(Note: any cells with conditional formatting will not be changed by the fill colours above)
I have recorded the above steps into a macro to give you a useful audit macro that could be adapted for future use. Click on the button on the Info tab to run the macro.
See these pages for information on macros.
3) Check the range C9:S9 for any inconsistent formulas
Select the range C9:S9, Go To Special, Row differences, fill the selection in yellow
4) Review the precedents for the formulas in row 25
Select the range C25:S25, Go To Special, Trace Precedents, Direct only
5) See if there are any charts in the spreadsheet
Select cell A1, Go To Special, Objects
6) Find the last cell
Select cell A1, Go To Special, Last cell
Added by Chandoo:
Do you use Go to Special?
I use go to special (both dialog box and keyboard shortcuts) all the time. It is a really easy way to navigate a complex workbook and quickly select what you want. My favorite uses of Go to special are, selecting blank cells, finding data validations, locking formula cells, formatting input cells (constants). To find conditional formatting I usually go to home > conditional formatting > manage rules and see all the formatting rules in current worksheet. For formula auditing I rely on audit toolbar & manual inspection of the workbook.
What about you? Have you used Go to Special? What are your favorite features? Please share using comments.
Thanks to Myles
Many thanks to Myles for compiling all the tips & sharing this with us. If you have enjoyed this article, please say thanks to Myles. You can also reach him at Excel Audit or his linkedin profile.
61 Responses to “Custom Number Formats (Multiply & Divide by any Power of 10)”
Okay, that is pretty awesome. Thanks for the tip Kyle & Hui!
This gets me closer to a solution for reporting financial in (000's) but I am still left with the rounding problem when adding these numbers. As an example 1,234,567 using 0,###, I get 1,235 which is great but when I sum 1,234,567 and 1,234,567 in this format 1,235 + 1,235 I get 2,469 instead of 2,470. 2,469 is technically correct, but for those who cannot see the full number the sum appears incorrect. Is there a fix for this?
Steve, I often have to display numbers in a similar fashion. I have to ask though, is it wise or even necessary to force the display to '2,470'? As long as it is understood that some rounding is going on, it should be acceptable that the numbers won't add up on screen. In fact displaying these incorrectly (outside of the realm of rounding) seems misleading?
Often, I will include somewhere on the page, prominently enough to be seen but not obtrusive, "In Thou." or "In Mils."
Alternatively you could make the custom format "#,K" which automatically suggests rounding.
I hear you, but that all numbers tie is absolutely expected at my company. As a former CFO explained to me years ago, if the numbers don't add up it brings into question the validity of the report (as he chucked my report into the trash). Experience has taught me that he was absolutely right. This can take Sr. Management and Board of Directors off the focus of what the report and presentation is about. So the hours it takes me to tick and tie the reports is well spent (and cheap) compared to the time spent and repercussions up the ladder. I wish we could show the complete numbers in these presentation reports (30 slides per quarter) but it is just too busy on the screen.
I do put (000's) at the top left of each report (we report in thousands).
Thanks for your response!
Well, there really is no way to solve it other than rounding to the nearest X. (since that is what Excel is doing with the format, rounding to the nearest 1000)
Here is a formula, for a total, that rounds each number in A1 to A100 to the nearest 1000 and then sums it up.
=SUMPRODUCT(ROUND(A1:A100,-3))
It has the same effect has rounding each individually but it leaves the individual items alone so you can keep accurate percentages.
You are right, the fact is we are taking away three places in each number by rounding to thousands and holes are naturally created that must be filled or explained. But it is still great to have the discussion. I learn about how others think and put another tool or two in my Excel toolbox.
I greatly appreciate this!!
Steve,
I have to agree with Cameron. I made a similar post yesterday morning that was lost with the server migration. There is a certain level of displayed inaccuracy that is expected when reporting in 000s or 00s. It is best to leave everything as is, which will provide the most accurate results. As Cameron suggested, stating it is in 000s would be the best approach, and if desired, stating that there may be rounding differences as well. I work in finance/accounting as well and I never round except for check totals (due to the floating point issues).
Kyle
I really appreciate your response. You are both correct with expected inaccuracies. In my situation, the inaccuracies are best done in plugs. If I have to round one of the larger numbers (say advertising) up or down 1 (1,000) to get the number to balance it is much cleaner and reduces questions. Of course it also takes more time and makes the process manual.
Try this:
=(ROUND(A1,-3)+ROUND(A2,-3))
Thanks for the post. I currently use the round commands (round, roundup and rounddown) but was hoping to get away from that. In a simple or single sum the round command works well but when I start using those rounded sums to get to a bottom line (Profit/Loss) or on a balance sheet, they can veer out of balance from actual final numbers more often than I like to see. It especially gets ugly in % change. I spend a lot of time verifying that everything tics and ties. I'm still going to try this format on my next financial presentation with the rounds to see how well it works.
Thanks again!!
Another handy technique is to use the text function. For example, to display a date in a format that you want, you can do something like:
=text( A1, "yyyy-MM-dd" )
Lots of potential variations there, but it lets you format A1 however you want it, without altering it.
side note, in some languages, I've seen other choices such as "jjjj-MM-dd" so if you care about internationalization, try to make your formatting easy to change by making the format text refer to a single cell.
Try setting precision as diplayed in the advanced options.
Thanks. This is a cool new feature to me, unfortunately in this instance the original number is lost to the rounded number (1,234,567 becomes 1,235,000). Great tip for future reference, I'm already thinking of ways I can use it.
Thanks again!!
Excellent tip! Thank you.
nice.. post
Regards,
Saran
lostinexcel.blogspot.com
Steve, this is an excellent tip. But with the % sign used to Multiply, the symbol still shows up in the cells, even with usage. Am I missing anything else?
@Dhakkanz
Did you put a Ctrl J in front of the % sign ?
The Ctrl J trick works but not very well if the space is limited. For example, I want to show 9E-12 as 9 (units is in picoseconds). If I limit the cell width to less than 10 characters then this trick does not work.
Any other suggestions if the space is limited?
this is very clever. i like the concept. there is something about the concept that makes me uneasy... i think it is reading this article after having finished the great series of articles on auditing and spreadsheet risk....
i recently stumbled on a software company that was addressing the rounding and or formatting creating incorrect sums (visible) issue. their solution seemed hard to implement and has costs but does work quite well. i think a well written vba program would be more flexible (for solving rounding/formatting issues). here is their link:
http://www.think-cell.com/products/round/overview.shtml
I was so happy to see such a custom format. I had not imagined that there would be such a simple solution to the problem. Thanks a lot.
This post will be very useful to Engineers like me who have to do a lot of electrical design calculations in excel. Very often we have to deal with *1000 or /1000 value of current or voltage For ex. millivolts mV, milliampere mA, milliwatt mW.
With this formatting trick, I can now remove all the *1000 or /1000 I had to add in my formulas to take care of the multiplier factor.
🙂
However,
I have one new problem using this custom format. My Excel uses German standard way of representing numbers i.e. a "comma" to denote decimal point and a "dot or punkt" to denote thousands ('1000s) placeholder. Alos, eventually the sheets I make will be read on German Excel only.
Can someone post a German equivalent to the same formatting?
Thanks again.
Thanks very much.
The trick was very usefull.
Problems will be very simple when they are solved!
Thanks again.
[...] Microsoft – Custom Number Formatting > Chandoo – Factor 10 number scaling > ASAP Utilities – Create a bulleted list > Daily Dose of Excel – Star Rating [...]
[...] Microsoft – Custom Number Formatting > Chandoo – Factor 10 number scaling > ASAP Utilities – Create a bulleted list > Daily Dose of Excel – Star Rating [...]
Hi Thank you for the tutorial. It is very useful and clear.
And I have a question related to this format.
Is it possible to multiply by a different number instead of 10?
I don't want to create a new column to do the multiplication.
Let's say it will automatically multiply 2 when I enter 19 and will show 38 in the cell.
Thanks for reading and hope you could help me.
OK so I'm trying to use this to format a cell so that this:
0.09
0.07
-0.14
Will display as:
9.0%
7.0
(14.0)
Looking to have the decimal points align but not show the % symbol after the first row. The problem with the above technique is that formating the wrap text will eliminate any placeholders you put in place to help with the alignment formating. Is there any workaround to this?
@Matt
Are the 3 values in the same cell?
If so I doubt you can do what you want
Different cells, just gave 3 as an example. Effectively the cell would multiply by 100 but still give the spacing as if a % were there, ie putting a _% effect at the end of the cell
@Matt
If I use the format
#,#Ctrl J%
I get:
9
7
-14
You have to enable Word Wrap in those cells
and then adjust the width accordingly
[...] Hi, You can do this with custom number formatting. There are limits to what you can do with it, but for your specific example, a format like this might work: 0000" / (4837)" To learn more about custom number formatting see: Create or delete a custom number format - Excel - Office.com Excel Custom Formats: Numbers/Text Formats in Excel Spreadsheets Custom Number Formats (Multiply & Divide by any Power of 10) | Chandoo.org - Learn Microsoft Exc... [...]
okay idk what i did wrong, maybe my excel is being stupid but its not working 🙁
i originally have 52.6625 (it's in thousands) so it's really 52,662.50.
if I put in the *1000 formatting above i get this result:
52,662.5%%%
if I try it without the carrots i get:
52,662.5CtrlJ%%%
Also if I go back into the custom formatting, it changed it to:
#,###.#%%%
I dont know why it put a "/" in between the Ctrl either. Any suggestions as to what I'm doing wrong??
Thanks!
nvm! im dumb and realized you don't type the control j but actually hit control+j
Dear Hui,
I have been reading your posts with awe. Thanks to Chandoos's work. I need your help. How do I custom format a number to display in lakhs as used in India.....123456789.00 as 12,34,56,789.00 (twelve crores thirty four lakhs fifty six thousand seven hundred eighty nine.....I understand excel will show numbers in thousands only....
Thanks
@Mathew
Try using the Google Custom Search box at the top right of every page at Chandoo.org
There are a few existing posts on Indian number formats including:
http://chandoo.org/wp/2010/07/26/indian-currency-format-excel/
This site is also very useful: http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/
Thanks for this tip. I have two questions:
1. I need to divide numbers by 1000 but leave no decimals.
At the moment the format I'm using from your site is # ##0.###.
However, this is leaving me with a result with a . on the end.
i.e 1000 becomes 1. instead of just 1 with no point on the end.
2. My spreadsheets has a number of pivot tables representing a sheet that automatically updates from the accounting database.
So lots of ways of analyzing sales.
Is there a way I can set the format once for a value instead of having to format on every sheet?
All help much appreciated
@Caron
A1. To divide by a thousand and have no decimals use code #,
Looking at your codes in a Non-English excel it may be #.
A2. You could try applying a style to the Pivot Table area
You should also read:
http://office.microsoft.com/en-au/excel-help/refresh-pivottable-data-HA101906071.aspx
http://answers.microsoft.com/en-us/office/forum/office_2003-excel/losing-formats-when-i-refresh-the-pivottable/372c5001-21ba-4ee1-b4f7-7c7758f8737f
Thanks! will give it a try.
Caron
Unfortunately it seems this work-around has now been semi-disabled in Excel 2010.
When you first enter the number format, the application accepts it and everything works fine. But if you save the file and re-open it: excel has modified the number format, and it breaks the functionality.
Specifically: all the formats that make use of a comma AND percentage sign in the same (sub)-format, excel now DELETES all trailing commas in the saved custom-number-format string. Hence when you reopen the file all formats like "mA" or "mV" are now displayed 1000x their actual value.
Example:
Try to apply: 0.0,"mV"%%%
Excel will strip the comma and save it as: 0.0"mV"%%%
(which obviously won't produce the desired effect)
Funny enough, excel WILL apply the correct number format TEMPORARILY to the open document: but if you re-open the file, POOF! all number formats wrecked.
(Perhaps older versions always saved the number format wrongly too, and they have changed the way number formats are refreshed upon opening the file. Or they could have purposely disabled this - who knows)
So sad. This was an almost perfect work-around.
I would be interested which versions of Excel this still works in.
Hi,
Is there a way to automatically include the 2 digit year into a custom format for invoice numbering ...?
Is there a trick to combine functions (such as today() or year()..) in a given custom format ?
Thanks
You could certainly see your skills within the work you write. The world hopes for more passionate writers like you who arent afraid to say how they believe. Always follow your heart. cgaedfkafgde
I think you have observed some very interesting details , appreciate it for the post. kabddabcdfag
Hi,
Is there a way to add 100 to only the number format? I have a graph which shows the diff from 100, e.g. 89 is -ve 11 from 100 and 139 is +ve 39. I want my graph to calculate based on -11 & 39 but shows 89 & 139. I can do this for the positive number using the format #,##100 but it shows -111 instead of 89 for the negative.
Not sure if I was very clear in what I was looking for, but any help is appreciated.
Thanks,
Roshan
just to update that, I need only the data labels for the graph to show 89 & 139
dear Sir
Thanks
Dear Sir
I read your article. I want to display absolute figures in lacs -
/1000000
Put 0.######,,,%% in Custom Number formatting
want Rs. 1662052 as 16.62
but getting as 1.6222052%%
Trying Ctrl+J as madam kyle suggested but %% are reflecting on excel screen and not getting hided so cannot send report to Top Management. Kindly assist. I think I am making a mistake in Ctrl+J step.
Thanks a lot in advance.
warm regards,
Deepak Vats
@Hui
Sir great work, you helped me alot.
Thanks for sharing wonderful tricks.
@Deepak
First make your cell vertical alignment to top + wrap text
then use this in custom format:
#,##0.00,,, %%
Press Ctrl+J before %
It will looks like:
#,##0.00,,,
%%
and you are done.
Regards,
Hi Guys,
I have created custom format ##,##0.0,,, CTRL + J %% to display
10000 as 0.1
100000 as 1.0
1000000 as 10.0
10000000 as 100.0
100000000 as 1,000.0
so forth.
The problem i am facing is that once i applied this format , save and close the workbook. the next time i open it the format get change like for 100000 it shows me 1.0 before closing the workbook ans 10.0 when i open it again.
please advice
VSM
Any help guys!
VSM
How can i attached the images here
I am trying to utilize this technique in order to calculate basis points. Basis points multiple % by 10,000 in order to find their value. For example, 1% would be 100 bps. I have a % field that calculates the bps % and then used the formatting of #,#.#%% to try and arrive at the formatting for bps. However, since the field is linked to a percentage, it is formatting as 100%%. I need the percentages to be removed and a "bps" to be replaced.
@Rachel
I don't believe that what you want can be done in 1 cell
You may have to use a helper column to assist you
Hi Hui - I don't have the option to create another field as I am already using a calculated field to arrive at %. To find the bps, I calculated MU% in a calculated field. I then pulled in the MU% field again and found the "Difference from" to find the % change. I then formatted the % change to x10,000 but it's not showing up correctly.
Great trick, thanks a lot!
I have used it to show pp instead of %,
my custom format looks like this:
0"pp?"%
I have noticed that this method works well with .xls files in compatibilty mode. However, I have had trouble with it using .xlsx files. Specifically, I can get the method to work in the newer formats on initial capture, but when I save, close, and reopen the workbook, it does not work after the reopen – the control-j seems to vanish.
Has anyone else seen this behavior?
Hi Mark,
We too are facing similar issue. We are using office 365 version of excel. Did you find any workaround for this issue.
With all the format codes I was finding I couldn't shrink the excel cell width without the numbers turning to #####.
Apply shrink to fit, then word wrap and all was good.
Firstly i would like to thank you for the solution you provided.
As Mark and Brian has mentioned, we too are facing the similar issue. The custom formatting disappears after saving and reopening the excel file. We are using Office365 version.
The custom format that we are using is :
0.#####,,,%%
0.#####0,,,%%
Any help would be highly appreciated.
Hi People,
The article is amazing!!
I am looking to divide numbers by 100.
I am using conditional formatting, wherein when a user selects "Hundreds" from the dropdown menu, entire numbers in all worksheets get divided by 100 and if one chooses, "Normal", normal numbers are displayed.
Now it is working properly, however, when I close and reopen the excel sheet, the entire formatting changes.
Any workaround?
I need to display normal as well as divide by 100 numbers.
I am using excel 365, any new function in it?
Thanks,
Rohit
I used this a couple of years ago to discover use of the comma. I had a vague recollection that you described how to go in the opposite direction.
When I couldn't figure out how to hide the percent signs even using , I downloaded your example file. That file is an xls file and it opened in compatability mode. I wonder if you have a solution for Office 365, or it I would have to save my file in an earlier version of Excel (I won't do it, BTW). Thanks.
Best regards,
Thanks for the formatting tips. Very helpful...I'll try to become awesome.