Summarise the quarter with a pivot table and a slicer
Total the fees by service with a pivot table, then use a slicer to take one person's work in and out of the totals.
✦ Course · Advanced
Analyse and model figures in the Sheet editor: pivots, Solver and what-if
Summarise a practice's jobs with pivots and slicers, chart and guard the data, then model rates and workload with Goal seek, Solver, scenarios and data tables.
Start courseYOUR OUTCOME
By the end you will have a sliced pivot of a quarter's jobs and a practice plan you can test with Goal seek, Solver, scenarios and a data table.
Summarise and slice job data with pivots Find a target rate and the best work mix Compare cases and a range of rates
8 min
Chart the fees with a pivot chart
Group the jobs by one column and chart the totals in the Visualisation pane, then export the picture before you close it.
Flag outliers and guard the inputs
Shade the hours and fees with conditional formatting, then stop bad entries with validation rules.
Work back from a profit target with Goal seek
Find the charge-out rate that brings the practice plan to its profit target.
Plan the quarter's work mix with Solver
Let Solver choose how many jobs of each kind to take on before staff and partner hours run out.
Keep named cases with the Scenario manager
Save sets of inputs, such as a base case and a busy season, and switch the plan between them.
Test a range of rates with a data table
Run the profit formula through a column of charge-out rates in one step.
What each lesson covers
The course, lesson by lesson.
Summarise the quarter with a pivot table and a slicer
Total the fees by service with a pivot table, then use a slicer to take one person's work in and out of the totals.
This course uses the Sheet editor in the desktop workspace on a computer. Select the whole data block, headings included, before you choose Pivot table in the More menu. Source range (incl. header row) starts from your selection. Put the Destination (top-left cell) clear of the data, pick one or more Rows fields (Ctrl-click for several), add a Columns field if you want a grid, and tick a value with its aggregation: sum, count, avg, min, max or countDistinct. Create writes the table with a Grand Total and recalculates it whenever the source changes. Insert slicer… then adds a card for one column of the source data, with a count of rows beside each value. Every value starts highlighted, and a click drops it from the totals or brings it back. Press ⌫ to restore them all before × removes the card, because a closed card leaves its filter on the pivot. An exported .xlsx holds the pivot as plain cells, not an Excel PivotTable, and leaves the slicer out.
Practice. Build a pivot of fees by service from your own job list, add a slicer on the staff column, then switch people off and on and watch the Grand Total move.
Select A1:F19, open More and choose Pivot table. Set Destination to H3, pick Service under Rows, tick Fees with sum and click Create. Then choose Insert slicer…, pick Staff, click Add, and click Nadia on the card.
Chart the fees with a pivot chart
Group the jobs by one column and chart the totals in the Visualisation pane, then export the picture before you close it.
Pivot chart, in the Insert group of the More menu, turns a column of labels and a column of numbers into a chart. Pivot chart · group by lists each column with its heading, such as Column B · Service, and takes the labels. Pivot chart · sum value column takes the numbers to add up, here Column F · Fees, and Chart type includes Bar, Line, Pie and Doughnut. The editor writes a small summary table two rows under the last used row, headed Service and Sum of Fees, then opens the Visualisation pane with the chart, titled Sum of Fees by Service. It is a snapshot. It doesn't follow later changes to the sheet, and the pane doesn't save your edits, so use Export for a PNG or PDF before you close the tab. When the figures change, delete the old summary table first, then run Pivot chart again. Pie and Doughnut suit parts of a whole, like a quarter's fees.
Practice. Make a pivot chart of your own fees by service, switch Chart type in the Visualisation pane to compare Bar and Doughnut, then export the one you prefer as a PNG.
Open More, choose Pivot chart, then Column B · Service, Column F · Fees and Doughnut. When the Visualisation pane opens, click Export and choose PNG.
Flag outliers and guard the inputs
Shade the hours and fees with conditional formatting, then stop bad entries with validation rules.
Select the range, then choose Conditional format in the More menu. Apply to range starts from your selection. Rule type covers cell value comparisons, text and date rules, top or bottom N, duplicates, a colour scale, data bars, icon sets and a custom formula. Each rule joins the list at the top, with ✕ to delete it. Data bars size each cell between the lowest and highest in the range, and 3 traffic lights marks each value red, yellow or green by where it falls in that span. An exported .xlsx leaves date rules out, turns icon sets into arrows and saves every text rule as contains. To guard inputs, choose Data validation. List puts a ▾ on each cell with the Allowed values you type. Whole number, Decimal, Date and Text length ask for a Condition, such as between, and its bounds, then On invalid entry. Reject (Stop) refuses the entry and names the cell, while Warn and Info keep it and flag the cell in red.
Practice. Add data bars to an hours column and an icon set to a fees column, then give a job-count column a Whole number rule with Reject (Stop) and try typing a fraction.
Select E2:E19 and add a Data bars rule. Set Apply to range to F2:F19, choose Icon set with 3 traffic lights and click Add rule. Then select D2:D19, choose Data validation, Whole number, between, enter 1 and 60, and pick Reject (Stop).
Work back from a profit target with Goal seek
Find the charge-out rate that brings the practice plan to its profit target.
Goal seek changes one input until a formula lands on the number you want. Build the model so the answer is a formula that depends on that input. In the More menu, under Tools, choose Goal seek. Set cell (formula cell) is the result, To value (target) is the number you want, and By changing cell (input) is the cell it may change, which must hold a number, not a formula. Solve searches outward from the input's current value and writes the answer into the input cell. It works best when the result keeps rising or keeps falling as the input moves. The toast names the value it found, and Undo puts the old input back. When no value in reach hits the target, Goal seek says so and leaves the input alone. It moves one cell only. To choose several inputs at once within limits, use Solver, the next lesson.
Practice. Build a small profit model of your own and use Goal seek to find the price, hours or headcount that reaches a target you set.
On the Plan tab, B5 holds =B1*B2*B3*B4 and B7 holds =B5-B6. Choose Goal seek, enter B7, 250000 and B1, and click Solve.
Plan the quarter's work mix with Solver
Let Solver choose how many jobs of each kind to take on before staff and partner hours run out.
Solver changes several inputs at once to make one result as large or as small as possible, within limits you set. Keep the decisions in variable cells that hold plain numbers, and make the objective a formula of them. In More, under Tools, choose Solver…. Set objective (formula cell) is the result, To picks Maximise, Minimise or Value of…, and By changing variable cells takes a range such as B2:D2. Write Constraints one per line, such as B9 <= D9, with a number or a cell on the right. B2:D2 int keeps whole numbers and bin allows only 0 or 1. Make unconstrained variables non-negative is ticked by default. When each formula adds up fixed amounts per variable, as SUMPRODUCT does here, Solver finds the exact best answer. Other models get a local search, and the toast says which. If no mix meets every line, nothing changes and the toast names the lines that clash. Solver keeps the model with the sheet, but Export XLSX leaves it out.
Practice. Raise the partner hours in D10 to 160, open Solver… again and click Solve. The model is still filled in, so you can see what ten more partner hours are worth. Then add D2 >= 26 as a last line and solve once more. Advisory is capped at 20 in D6, so the toast lists D2 <= D6 and D2 >= 26 as lines that can't all be true at once, and nothing changes.
On the Mix tab, choose Solver…. Set objective B8, To Maximise, variable cells B2:D2, and type these constraints: B9 <= D9 B10 <= D10 B2 <= B6 C2 <= C6 D2 <= D6 B2:D2 int Then click Solve.
Keep named cases with the Scenario manager
Save sets of inputs, such as a base case and a busy season, and switch the plan between them.
The Scenario manager lists the sets of inputs saved with the sheet. Open it with Scenarios… (What-if) in the Tools group of the More menu. + New scenario asks for a Scenario name and Cell values, one per line in the form B1=185. Save adds it to the list with its first few values under the name. Show writes every value in the scenario into its cell, so the model recalculates at once, and Undo takes it back. Edit changes the name or values, and Delete asks first. Show overwrites the inputs, so save the figures you have now as a scenario before you try another. List every input a case touches in every scenario, so switching back restores all of them. Scenarios are saved with the spreadsheet and come back when you reopen it. They stay in Oppermind, though. Export XLSX leaves them out, so type the cases into cells if the workbook is going to someone else.
Practice. Save your current inputs as Base case, add a second case that changes two of them, then switch between the two with Show and watch the result cell.
Choose Scenarios… (What-if), click + New scenario and save Base case with B1=185, B2=95 and B4=0.9 on separate lines. Add Busy season with B1=185, B2=110 and B4=0.85. Click Show on Busy season, then Show on Base case.
Test a range of rates with a data table
Run the profit formula through a column of charge-out rates in one step.
A data table works one formula through a list of inputs and writes a result beside each. Lay it out as Excel does. For values down the side, type the inputs in a column, then put the formula one row above the first input and one column to the right. Choose Data table… (What-if) in the Tools group. Table range (incl. inputs + corner) runs from the blank cell above the inputs to the last result cell, and Column input cell names the model cell the inputs stand in for. OK fills the results and leaves the model's own inputs as they were. For values across the top, fill in Row input cell instead. For two inputs, put values down the side and across the top with the formula in the top-left corner, and fill in both. A second formula beside the first gets its own column of results. The results are plain numbers, so run the table again after you change the model.
Practice. Put fee revenue as =B5 in F2 beside the profit formula, widen the Table range to D2:F7 and run it again, so each rate shows both figures.
With Base case showing, type Rate in D1 and Profit in E1, the rates 175, 180, 185, 190 and 195 in D3:D7, and =B7 in E2. Choose Data table… (What-if), enter D2:E7 as the Table range and B1 as the Column input cell, then click OK.
Keep going
Make the learning stick
Start with one real piece of work.
Open Oppermind beside the lesson, apply each step, and leave with something you can use.