How to Calculate Each Item's Share of the Total in Excel

Work out what percentage of total revenue each category brings in, the share of a whole group, and which items are big contributors.

You will learn to

• Divide a part by a locked total
• Calculate the share of a group of categories
• Count items above a share threshold

How it works

An item’s share of the total is its value divided by the grand total: =B2/SUM($B$2:$B$7), formatted as a percentage. Lock the total range with dollar signs so you can fill the formula down and every row divides by the same total. All the shares together add up to 100%.

To get the share of a group — all service lines, all online channels — total the group with SUMIF and divide by the grand total. Share-of-total analysis is how managers see their revenue mix and decide where to focus.

=B2/SUM($B$2:$B$7)