# How to index values to 100 from a base year in Excel

Divide each value by the locked base value and multiply by 100: `=B2/$B$2*100`.

**Excel:** `=B2/$B$2*100` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Year | Avg. price per gallon | Index (2019 = 100) |
| 2019 | 3.2 |  |
| 2020 | 3.35 |  |
| 2021 | 3.68 |  |
| 2022 | 4.15 |  |
| 2023 | 4.32 |  |
| 2024 | 4.4 |  |
| 2025 | 4.56 |  |

Formula in C2, filled to C8: `=B2/$B$2*100`

Result: 100.0; 104.7; 115.0; 129.7; 135.0; 137.5; 142.5

## How it works

An index shows every value relative to a base: the base year is 100 and a later value of 142.5 means 42.5% higher than the base. `$B$2` is locked so every row divides by the 2019 price. Indexing puts series with different units on one chart, since they all start at 100.

## Other ways

- `=B2/$B$2-1`: The change since the base year as a percentage instead of an index. (Shows 0.425 (42.5%) for 2025 instead of 142.5.)

## Common mistakes

- Without the $, the base moves down with the formula and every row compares with the year before.
- Two indexes can only be compared if they share the same base year.

## Questions

**How do I change the base year?**

Point the locked reference at that year's row, for example `=B2/$B$5*100` for 2022.

**How do I turn the index back into a percent change between two years?**

Divide the indexes: `=C8/C5-1` is the change from 2022 to 2025.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/index-values-to-100