ixixxsx
ixixxsx

Reputation: 43

Showing percent difference in values in excel

I have a table and in Column 1 is a list of companies, and company names may appear more than once because Column 2 is a year. Column 3 is the amount a company spent in that respective year. How can I show the percent differences between each year without spilling into the next company? For instance Company, A is 2017-2020 and i want to show the % growth for that. But the next row is Company B for 2017-2020 and I don't want to calculate the % between the values for 2020 Company A and 2017 Company B.

Upvotes: -1

Views: 232

Answers (2)

user14581327
user14581327

Reputation:

To calculate the % between the values for different companies:

  1. Sort the data by company.
  2. Find the percentage of the amount. Select the cell. Click Home > Number Format > Percentage. Enter the formula in cell C2: =B2/A2.
  3. Find the percentage of change between two amounts. Select the cell. Click Home > Number Format > Percentage. In cell B3, divide the company A's second year’s sales by the first year, and then subtract 1, or enter the formula in cell C3. =(B2/A2)-1.

Read more about calculating percentages at support.microsoft.com. Hope it helps, Please upvote!

Upvotes: 2

You can use a Pivot Table for this:

enter image description here

As you can see, Company A invested 1000 on 2017 and 2000 on 2018. So 2000-1000=1000 and '1000 / 2017 investment (which is 1000)= 100%'

Company A invested 3000 on 2019 and 2000 on 2018. So 3000-2000=1000 and '1000 / 2018 investment (which is 2000)= 50%'

And so on.

About Pivot Tables, check:

Create a PivotTable to analyze worksheet data

My setup in Pivot Table is:

enter image description here

  1. Field Company into rows section
  2. Field Year into Columns Section
  3. Field 'Amount' into Values section
  4. IMPORTANT: Field Amount must change setup. Change option show values as and choose % difference with previous year

enter image description here

enter image description here

Upvotes: 0

Related Questions