Donate SIGN UP

Excel Percentages

Avatar Image
patrickstar | 09:44 Thu 21st May 2020 | Technology
33 Answers
In Excel how can I work the percentage of cell B of cell A in numerous rows that have differing values?

For examplem, in row 1 I know to enter in C1 '=B1/A1' for the percentage but can this be repeated for numerous rows where the value in Column A and B differ all the way down?

For whatever reason I cannot work it out without the same % figure in column C1 repeating all the way down.

Hope that makes sense! Thank you


1 to 20 of 33rss feed

1 2 Next Last

Avatar Image
Try this, then. On the top of the page where you see "File Home Insert..." towards the right you will see "Formulas". Click this. Then over on the right you should see "Calculation Options". Click that and you should get a dropdown with "Automatic; Automatic except for data tables; Manual". Make sur Automatic is ticked.
14:24 Thu 21st May 2020
Formula in C1 should be B1/A1*100 to give perecentage. Then place the mouse cursor over bottom right corner of Cell C1 and make sure the cursor changes to a black cross, then left click and drag down the C column to replicate the formula as far as you want. Values in Column A & B can now be entered and the resulting percentage will appear in column C
> For example, in row 1 I know to enter in C1 '=B1/A1' for the percentage but can this be repeated for numerous rows where the value in Column A and B differ all the way down?

Yes, C2=B2/A2, C3=B3/A3 and so on all the way down. Set the cells to display as a percentage.

If you highlight C1 and fill down to say C10 (assuming rows 1 to 10 are already filled for columns A and B), Excel will automatically fill in the correct formulas in cells C2 to C10 for you.
Question Author
Thank you, but when left click dragging the cell down the value remains as per the original cell and not the percentage value that equates to those in the other two adjacent columns for each row. Probably a school boy error but unable to rectify.
Just right click on C1, select copy and copy down into C2, C3, etc.
If you have data in A2, B2 then A3, B3 etc it will calculate c2, c3, etc
Question Author
Hi fiction-factory, same problem still exists. In this instance Cell C1 has the value 6.03 but when trying to populate C2, C3, C4 etc. the same figure of 6.03 is being populated. Not sure if I am getting cabin fever from the lock-down, heat stroke from the warm weather or my brain has just given up trying to interact with the rest of me!
Its a command I have used numerous times in the past but is seemingly lost somewhere in my head!
When you copy the first one, make sure you paste as formula, then copy to the rest of the cells
//Formula in C1 should be B1/A1*100 to give perecentage. //

Or simply set the cell format to "Percentage" and then you can forget the "*100"
yeah the funnest fun is when you have a column A-E and percents next to them, and you do the first formula next A and click! on the L corner
and it goes proooop! and fills with formulas down to E

magic - you need to do Excel is FUn - Mike Girvin - he has thousands of viddies on excel. You will find it life changing!

it souns as tho you have no idea what a $ sign does in excel and you need to know that too - see Mike Girvins site -
no I am not Mike Girvin
NJ - yeah you need to use percent - before you put in the no

this is to my warped mind NOT intuitive which is why one M Girvin esqs site is so useful
when you drag C1 down can you tell us exactly what you see in cell C2.
i mean the formula, not the actual value.
I've just set one up and it works fine

1 50 2.00%
3 200 1.50%
4 50 8.00%
4 100 4.00%
3 600 0.50%

Maybe in c1 instead of A1/b1 you have put in numbers- 1/50 using my example
>For examplem, in row 1 I know to enter in C1 '=B1/A1'

I'm not sure what the inverted commas are there for- are they in your formula?
here's a demo, in the top picture note the little square at the bottom right of C1, left click that and drag down, ie keep finger on the left mouse button. You should see the second picture. Also see cell 2 has been updated automatically. Compare yours with this.
Question Author
Again thank you all.
I have checked the formula in Column C and each row shows the correct formula....with the row number changing for each row whilst the column stays the same.
I have made all three columns function as 'Numbers' and differing combinations just to get it to work.
I have now resorted to hitting the keyboard with a cardboard tube which is fun but obviously gets me no closer to resolving the issue. It is something stupid I am doing (or not doing), that I am aware, which makes it even more frustrating!
Have you checked that the cell column is not locked?
Question Author
Hi AuntPollyGrey, no they are not locked but was worth checking. Thank you
It ought not give the same value when you drag down. Maybe you have a setting that's set not to auto-update.

You don't need to *100. Format the cells or column to be percentage.
stupid question but the numbers in cols a/b are different? what happens if you change a number in col a/b?
also are they close to each other, decimals getting rounded etc? format the cells to 4 decimal places etc etc.

1 to 20 of 33rss feed

1 2 Next Last

Do you know the answer?

Excel Percentages

Answer Question >>