# Formatting ratios

To show the result of division as a ratio, you can use a custom number format. For example, the current ratio is current assets divided by current liabilities. Typically, this ratio is shown as x:1, or like this

The custom number format applied to the current ratio is set by using Format>Cells>Number

This format works because the current ratio is always shown compared to one. If you want to show a ratio, but the denominator is unknown, you can use tip81 from The Spreadsheet Page.

## 30 thoughts on “Formatting ratios”

1. Thank you! The ratio set up worked perfectly!!!!!!!!!!!!!!!!!!!!
Paul

2. Dan Oltman says:

This reminded me exactly what to do. I knew it was right the second I saw it. Thank you very much, job well done.
dano

3. Nubiedu says:

I have been trying to figure out for days how to do ratios in excel and I was so glad when I came across this…Thanks to whoever posted this

4. Leonard says:

Thank you for making this function so simple and so easy. I was able to perform a task which would normally have taken me an hour to research in only a matter of five minutes. Much appreciated!

5. kc says:

Thank you

6. frank says:

Another amazing formatting trick: type a ratio, e.g. 6/5, and precede it by a 0, that is: 0 6/5. See what Excel displays.

7. Gail K says:

Hi, this custom type does not exist in Excel 2007; any idea how to display a ration in this version?

8. You have to create custom types yourself (although MS creates some to get you started). Choose Custom and type this in the textbox.

9. Sarah says:

Thank you so much for posting this. It was a big help with my accounting project!

10. Ifthar says:

Here is another way of doing this
=TEXT(B1/B2,”0.0?)&” : 1?

11. Keith says:

I came up with this when wanting to show the ratio between two numbers

=”1:”&(A1/B1)

then refined it to show the ratio to only two decimal places

=”1:”&ROUND(A1/B1, 2)

12. eliz says:

what if the denominator is 0? It gives me an error.

13. Michelle says:

Hi
I’m new to this blog page.
I’m am desperately looking for a ratio on how to attribute a full dollar amount to various departments as a percentage.
The cost of stationery is \$1200 and I have many departments to allocate a percentage to based on what they have spent.
e.g.
\$1200 has been spent by the Admin Department and I have to % allocated the cost as user pays to each deparment.

The total is \$1200 to distribute
HR spent \$410.00
Computing \$250.00
soccer \$315.00
Netball \$90.00
Gardening \$45.00
etc.
What is the excel sum to correctly attribute a percentage of the \$1200 cost to the various departments.
Michelle
Mvick@value.net.nz

14. Michael says:

Hi Michelle –

It looks to me like it would “department spent/total spent”. That is

\$410/\$1200, \$250/\$1200, \$315/\$1200, \$90/\$1200, \$45/\$1200 etc.

And since that only totals 93%, etc pays 7% ;-)

Am I missing something here?

…mrt

15. Khuwair says:

This equation formats the cell to read for example 10:1. How can I get it the other way around – as in 1:10? Thanks

16. Gordon says:

@Khuwair – Just reverse it:

“1 : “0.0

17. Khuwait says:

Thanks Gordon. It worked perfectly!

I tried reversing it but got the order of the quotation marks wrong. I did 1:”0.0? and the result was 1:00!

18. Michelle says:

HI Everyone.

It doesn’t appear to be working for what I require. Maybe the way I communicated the query.
If I have a total spent expense and I need to divide it over the various departments that have lesser or higher spent. Then the total % has to add up to 100% with none left over. So when I sum up the percentages against the departments it should equal 100%.

Example
total money spent
1966

Department 1 has spent 850 ? xxx% of 1966
Department 2 has spent 950
D. 3 has spent 50
D. 4 has spent 64
D. 5 has spent 52

the percentage ought to value to 100%
Cheers

19. Michelle says:

Hello Michael.

This is what you wrote:
It looks to me like it would “department spent/total spent”. That is

\$410/\$1200, \$250/\$1200, \$315/\$1200, \$90/\$1200, \$45/\$1200 etc.

And since that only totals 93%, etc pays 7%

Am I missing something here?

I used your idea and finally it worked. =total spent/total of all departments (absolute) / 100
I tried to post the sum graphic but unable to.
many thanks.

20. Beezlebob says:

Thanks – just what I was looking for.

21. Maria says:

Hi

I’ve been going through these responses and have tried a few things. Some worked, some didn’t. I have a two part question, though. (1) I am trying to calculate the ratio between two numbers. My spreadsheet looks like this:

# Registered Users  # Published Works   Ratio

3,209                         2,049         1:1.57
468                        749          1:0.62
173                        492          1:0.35
148                        421          1:0.35

I used the ratio formula =”1:”&round(a1/b1, 2). My question is: Is this the correct formula? I know nothing about ratios but I feel like the ratios are incorrect for some reason.

(2) Is there a way to calculate the average of this list of ratios, and present it in ratio form? My boss asked for this and I have no idea if it’s even possible.

22. You just have them backward. To get “1? you need to divide the first number by 3,209. To get the second part of the ratio, you have to divide the second number by the same thing: 3,209. So

=”1:”&TEXT(B1/A1,”0.00″)
23. Richard says:

I have 200 staff members and 189 desks. I want to know the ratio of staff to desks. I don’t want it drilled down to :1 as the UK Governement average is 8:10 desks. I need to know what my ratio is as a whole on the floor so I can compare.

Using Excel 2007 – what do I need to do to get the ratio and for it to display as ‘Staff’:’Desks’ ?

Thanks
Richard

24. Rick Rothstein (MVP - Excel) says:

@Richard,

In order to produce a ratio to something other than :1, you need to set one of the ratio items and calculate the other. To understand why, look at your example 8:10… that is identical to 16:20 and 4:5 as well as infinitely many other ratios. Here is a general set-up that will allow you to specify the right-hand number (the 10 in 8:10) and calculate the other. Put your staff member count (200) in A1 and your desk count (189) in A2, then put your right-hand target ratio value (10) in B1 and then finally put this formula in whatever cell you want to display the ratio in…

=B1*A2/A1&”:”&B1

You can produce other ratio displays by changing the value in B1 if 10 is not the target value you want for the right-hand number.

25. Reggie says:

Thank you for this discussion. I helped me out of jam!

26. PJ says:

to approximate the above ratio to a whole number : 1 try this below

=ROUND(B1/B2,0)& “: 1? (put a space between & and “, and also between : and 1)

so you will get 2:1 above instead of 2.2:1

but if you want the ratio to one decimal place try this:

=ROUND(B1/B2,1)& “: 1?

if you want the ratio to two decimal places try this:

=ROUND(B1/B2,2)& “: 1?

etc

27. Thiero says:

i have been trying to divide 1000 in the ration 2:3 but it is not working. What can i do?

28. Thiero says:

Maria maybe you should try this one =A1/GCD(A1,B1)&”:”&B1/GCD(A1,B1)

29. Dragonfly says:

Thank you everyone. These suggestions have all been really helpful.

30. Brian says:

I am unsure if this already has been posted. What I am looking to do is set up an excel sheet where I can have if figure the “part to whole” ratio. For example if my ratio is 10:1 and I need a total of 2.5 lbs. I just have not found a spot for how to have excel calculate the the necessary amount of each part to make the whole. Any suggestions?

Posting code? Use <pre> tags for VBA and <code> tags for inline.