Exceljet

Quick, clean, and to the point

Pivot table project estimate

A pivot table is a handy tool for estimating projects. As long as the data is well structured, a pivot table can easily handle hundreds or even thousands of line items. You can group these items by category, by phase, by contractor, and so on. Once the pivot table is created, you can easily adjust filters to show only parts of the project as needed. In the example shown, the source data is extremely basic, and a pivot table is used to group by area and display a sum plus a percentage of total.

Fields

The source data in the example contains three fields: Area, Item, and Cost. The pivot table as shown uses only two of these fields: Area and Cost.

Field list for project estimate pivot table

Area is a Row field, and Cost has been added twice as a Value field, once to show total cost, once to show a percentage breakdown. To show total estimated cost, the Cost field is set to sum values and has been renamed "Estimate":

Cost value settings, renamed "Estimate"

To show a percentage breakown, the Cost field is added again to the Value area and renamed "%". Show Values As is set to percentage of grand total:

Cost value settings for percentage, renamed "%"

Finally, the Area field is sorted in descending order by Estimate (Cost):

Sort options for Area field

Steps to create this pivot table

  1. Create a pivot table
  2. Add Area as a Row field
  3. Add Cost as a Value field
    1. Rename to "Estimate"
    2. Summarize by Sum
    3. Set number formatting to currency
  4. Add Cost as a Value field
    1. Rename to "%"
    2. Summarize by Sum
    3. Display Percent of Grand Total
    4. Set number formatting to percentage
  5. Sort Area by Estimate

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.