EXCEL INTERMEDIATE: FULL-DAY PRACTICAL Keith’s Training Hub Original fictional teaching material Welcome 09:30–09:40. Close 16:15–16:30. Session windows follow the supplied programme. Individual lessons are self-paced; facilitators allow time for demos, practice, breaks and lunch. Use copies of the supplied fictional workbooks. No real payroll or personal data. Steps mainly use Microsoft 365 desktop Excel for Windows. Other versions differ. Optional checks are not required. These files do not inspect your work or issue a certificate. SESSION 1: More functions (09:40–11:40) Count matching orders with COUNTIF (15 minutes) Find how many orders are Open without counting by eye. LEARN A conditional count answers one clear question about a list. It does not add costs. EXAMPLE Orders contains eight fictional training orders. Status is in E2:E9. COUNTIF checks each status in that range and counts each match for "Open". It counts 6 Open orders. The other two, TR-102 and TR-106, are Closed. STEPS 1. On the Summary sheet, select B2. 2. Enter =COUNTIF(Orders!E2:E9,"Open") and press Enter. 3. On the Orders sheet, count the six Open entries in column E. 4. In a spare cell on Summary, enter =COUNTIF(Orders!E2:E9,"Closed"). It should give 2. 5. Check that the two counts add up to all eight orders. TRY IT Count North team orders using Orders!B2:B9. Explain why this result differs from a cost total. CHECK YOUR RESULT The North count is 3. Your formula counts labels in the Team column, not amounts. You can explain the range and criterion. IF IT DOES NOT WORK If the result is zero, check the spelling, spaces and chosen column. A filter does not make COUNTIF count only visible rows. OPTIONAL CHECK What does COUNTIF count here? 1. Matching records 2. The total cost of matching orders 3. Only visible rows after filtering Answer: 1. COUNTIF counts cells meeting one criterion. It is not a visible-row total. Microsoft guidance: https://support.microsoft.com/en-us/excel/get-started/use-the-countif-function-in-microsoft-excel --- Add matching costs with SUMIF (15 minutes) Calculate the North team cost and reconcile the whole list. LEARN SUMIF checks one range and adds the corresponding numbers from another range. EXAMPLE North costs are £120, £80 and £50: £250 in total. In Summary!B3 enter =SUMIF(Orders!B2:B9,"North",Orders!D2:D9). In Summary!B4 enter =SUM(Orders!D2:D9). The total is £540. STEPS 1. Use Team as the condition range and Amount as the sum range. 2. Enter the North formula in Summary!B3. 3. Keep both ranges aligned to rows 2–9. 4. Repeat the formula in spare cells for South and West. 5. Check £250 + £200 + £90 = £540. TRY IT Find the cost of Open orders by testing Status. Keep Closed orders in the source list. CHECK YOUR RESULT Open orders total £400. Your condition range is E2:E9 and your sum range is D2:D9. Open £400 plus Closed £140 equals £540. IF IT DOES NOT WORK Do not swap the condition and sum ranges. Amounts must be numbers, not text containing a pound sign. Format the numbers as currency instead. OPTIONAL CHECK Which range supplies the pounds to add? 1. Team B2:B9 2. Amount D2:D9 3. Status E2:E9 Answer: 2. SUMIF checks Team or Status, then adds the corresponding Amount cells. Microsoft guidance: https://support.microsoft.com/en-us/excel/functions/sumif-function --- Use IF to label a result (15 minutes) Flag amounts of £100 or more with a readable review label. LEARN IF chooses between two results. Write the boundary clearly so that exactly £100 is handled correctly. EXAMPLE Practice rule only: amounts of £100 or more need Review. This is not an organisation policy. In Orders!H2 enter =IF(D2>=100,"Review","Within limit"). TR-101 (£120) returns Review. TR-102 (£90) returns Within limit. STEPS 1. Check the original order: TR-101 is in row 2. 2. Enter the example in H2. 3. Copy H2 down to H9. Check that D2 changes to D3, D4 and so on. 4. On a spare practice row, test 99, 100 and 101. 5. Keep those test values outside the eight source records. TRY IT Explain the three boundary results, then inspect all eight order labels. CHECK YOUR RESULT 99 gives Within limit; 100 and 101 give Review. Only TR-101 in the original dataset is marked Review. Both labels are quoted text. IF IT DOES NOT WORK Use >= rather than > to include exactly 100. If every row refers to D2, remove an unintended fixed reference before copying. OPTIONAL CHECK Which test includes exactly £100? 1. D2>100 2. D2>=100 3. D2<100 Answer: 2. >= means greater than or equal to. A strict > test excludes the boundary. Microsoft guidance: https://support.microsoft.com/en-us/excel/functions/if-function --- Use TODAY without losing a reporting date (15 minutes) Calculate days until a due date and distinguish live dates from a fixed snapshot. LEARN TODAY changes as Excel recalculates on a new date. A repeatable report needs an explicit fixed date. EXAMPLE =TODAY() returns the current date, not the date this course was written. It takes no arguments. In Orders!I2, =F2-Settings!$B$3 returns 7 when Settings!B3 is 3 November 2026. The live version is =F2-TODAY(). Its result depends on the actual day. Negative means overdue; zero means due today. STEPS 1. Check that F2 and Settings!B3 are real Excel dates. 2. Enter the fixed-date formula in I2; format I2 as a number with zero decimals. 3. Copy it to I9. Keep Settings!$B$3 fixed. 4. Check the fixed results: 7, 9, 2, 15, 4, 12, 17, 8. 5. Try =TODAY() and =F2-TODAY() in spare cells. Do not expect the live result always to be 7. TRY IT Explain which formula you would keep in a dated report and which you would use for a live to-do list. CHECK YOUR RESULT You can explain why TODAY changes. The fixed-date calculation is reproducible. Days are displayed as numbers, not calendar dates. IF IT DOES NOT WORK If days look like a date, change the result format to Number. If dates are text, fix the date input; formatting alone does not always convert it. OPTIONAL CHECK Which result should stay reproducible for the 3 November snapshot? 1. =F2-TODAY() 2. A value inferred from the file name 3. =F2-Settings!$B$3 with the fixed date retained Answer: 3. The explicit fixed date preserves the reporting basis. TODAY is useful for a live view. Microsoft guidance: https://support.microsoft.com/en-us/excel/functions/today-function --- SESSION 2: Managing datasets (11:40–14:30) Sort whole records safely (15 minutes) Show the largest amount first without separating an order from its details. LEARN A single-column sort can attach the wrong amount to a person or order. Always move the complete record. EXAMPLE The full record range is Orders!A1:I9, including headings and your new formulas. Largest amount first starts TR-101 £120, then TR-102 £90, then TR-103 £80. Eight records and the £540 total must remain unchanged. STEPS 1. Save a new practice copy before sorting. Clear any filters. 2. Select A1:I9. Choose Data > Sort and confirm My data has headers. 3. Sort Amount (£), Cell Values, Largest to Smallest. 4. If Excel warns about a partial selection, expand it and check all nine columns. 5. Inspect the first and last records and recheck the count and total. TRY IT Record the full amount-descending order of IDs. Then restore Order ID A to Z before the next formula exercise. CHECK YOUR RESULT Order: TR-101, 102, 103, 108, 104, 106, 105, 107. TR-107 remains West, Cables, £30, Open. All eight records and £540 remain. IF IT DOES NOT WORK If IDs and amounts no longer match, undo immediately or reopen your saved practice copy. Do not repair mixed records by guessing. OPTIONAL CHECK Which selection is safest? 1. D2:D9 only 2. A1:I9, with headers 3. The visible amount cells only Answer: 2. The full record range keeps all related fields and derived columns together. Microsoft guidance: https://support.microsoft.com/en-us/excel/sort-data-in-a-range-or-table-in-excel --- Sort by team, then amount (15 minutes) Group each team and put its largest orders first. LEARN The first sort level makes the groups. The next level orders records within each group. EXAMPLE First level: Team A to Z. Second level: Amount Largest to Smallest. North comes first with TR-101 £120, TR-103 £80, TR-106 £50. South follows with £90, £70, £40. West follows with £60, £30. STEPS 1. Select the complete A1:I9 range and open Data > Sort. 2. Set Team, Cell Values, A to Z. 3. Select Add Level. Set Amount (£), Largest to Smallest. 4. Check Team is above Amount in the level list. 5. Apply once, then check each team and the overall £540 total. TRY IT Explain why running two unrelated single-column sorts is not the same as specifying two levels. CHECK YOUR RESULT The groups are North, South, West. Each team is in descending amount order. The original fields stay paired with the correct ID. IF IT DOES NOT WORK If amounts are sorted globally, inspect the level order. Move Team above Amount and apply the full-range sort again. OPTIONAL CHECK Which sort level controls the main groups? 1. The last level 2. The first level 3. Whichever column has the highest value Answer: 2. Excel applies the higher level first. Here Team creates groups, then Amount orders each group. Microsoft guidance: https://support.microsoft.com/en-us/excel/sort-data-in-a-range-or-table-in-excel --- Put priorities in a custom order (15 minutes) Show High, Medium, Low instead of alphabetical priority order. LEARN Alphabetical order puts Low before Medium. A custom list expresses the work order you intend. EXAMPLE Practice priority order: High → Medium → Low. High orders are TR-101 and TR-105. Medium orders are TR-103, TR-106 and TR-107. Add Order ID A to Z as a second level for a predictable order within each priority. STEPS 1. In desktop Excel, select the whole range and open Data > Sort. 2. Choose Priority. Under Order choose Custom List. 3. Enter High, Medium, Low in that order, one entry per line, or select the list if present. 4. Add Order ID, A to Z as a second level. 5. Check all three groups and the eight-record total. TRY IT Write the expected ID order and explain why A to Z on Priority is not suitable. CHECK YOUR RESULT Expected order: 101, 105, 103, 106, 107, 102, 104, 108. Within each priority, IDs ascend. No source priority has been changed just to force a sort. IF IT DOES NOT WORK Custom-list commands vary by platform. If unavailable, follow the worked example or use desktop Excel. Do not purchase an upgrade for this lesson. OPTIONAL CHECK Why use a custom list? 1. To delete Low orders 2. To change the amounts 3. To sort by a meaningful non-alphabetical sequence Answer: 3. A custom list supplies the intended order. It does not change the underlying labels. Microsoft guidance: https://support.microsoft.com/en-us/excel/sort-data-using-a-custom-list --- Filter a list and restore every row (15 minutes) View North Open orders, then return to the complete dataset. LEARN Filters hide records. Hidden rows are still in the workbook and may still be included in formulas. EXAMPLE North AND Open shows TR-101 (£120) and TR-103 (£80). The visible matching amount is £200. The full list still totals £540. The Summary COUNTIF for Open still returns 6, even when fewer rows are visible. STEPS 1. Restore Order ID A to Z. Select A1:I9 and choose Data > Filter if arrows are absent. 2. Use the Team arrow to show only North. 3. Use the Status arrow to show only Open. 4. Inspect both records and their values. 5. Clear both filters. Confirm all eight records return. TRY IT Filter Status to Open and Amount to Greater Than 50. List the matching IDs, then clear every filter. CHECK YOUR RESULT TR-101, TR-103, TR-104 and TR-108 match. Their amounts total £330. All eight records return after clearing. IF IT DOES NOT WORK A second filter narrows the first result. If rows seem missing, check every filter icon before assuming data was deleted. OPTIONAL CHECK Does filtering delete records? 1. Yes, unless you save a copy 2. No, it hides non-matching rows 3. Yes, if more than one filter is used Answer: 2. The underlying records remain. Always clear filters before checking the full dataset. Microsoft guidance: https://support.microsoft.com/en-us/excel/quick-start-filter-data-by-using-an-autofilter --- Make a safe dropdown with data validation (20 minutes) Allow only Open or Closed as typed status values, then test the rule. LEARN Consistent labels make counts and filters reliable. Validation reduces mistakes but does not prove all data is valid. EXAMPLE The approved exercise labels are Open and Closed. Apply a List rule to Orders!E2:E9 with source Open,Closed and a Stop error alert. Typing Pending should be rejected. Existing and pasted values need separate checks. STEPS 1. Save a separate practice copy. Select E2:E9. 2. Choose Data > Data Validation. Set Allow to List. 3. Enter Open,Closed as the source; your regional separator may differ. Enable the in-cell dropdown. 4. Set a clear input message and a Stop error alert. 5. Test Closed, then restore the original value. Test Pending and cancel the rejected entry. 6. Check all original statuses against the source list again. TRY IT In the practice copy, add a separate priority dropdown to G2:G9 using High,Medium,Low. Test an invalid typed priority without keeping it. CHECK YOUR RESULT Valid labels can be selected. An invalid typed value produces the Stop alert. Original statuses and priorities are restored. IF IT DOES NOT WORK Colour or a dropdown alone is not validation. Check the selected range, rule and error style. Pasting can bypass or replace validation, so review imported values separately. OPTIONAL CHECK What does a dropdown prove? 1. All historical and pasted data is correct 2. The workbook is secure 3. It guides entries; you still need data checks Answer: 3. Validation is an entry aid, not an audit or security boundary. Microsoft guidance: https://support.microsoft.com/en-us/excel/get-started/apply-data-validation-to-cells --- SESSION 3: Linking (14:30–15:30) Link cells instead of retyping (10 minutes) Use a cell reference and see how a result responds when its input changes. LEARN A formula refers to a source value. Retyping a number creates a separate value that can become outdated. EXAMPLE Summary!B5 is the fictional base amount 200. In a spare cell D5, enter =B5. It displays 200. Change B5 to 210: D5 follows. Restore B5 to 200. STEPS 1. Work on Summary. Check B5 is 200. 2. Select D5 and enter =B5. 3. Change B5 to 210 and check D5. Restore 200. 4. Copy D5 down one row and inspect the formula: it becomes =B6. 5. Try =$B$5 in another spare cell to see a reference that stays fixed when copied. TRY IT Explain the difference between a typed 200, =B5 and =$B$5. CHECK YOUR RESULT The link responds to a source change. You know which references shift when copied. The base amount is restored to 200. IF IT DOES NOT WORK If the formula displays as text, check the cell is not Text format and has no leading apostrophe. Re-enter it after correcting the format. OPTIONAL CHECK What keeps B5 fixed when a formula is copied? 1. =B5 2. =B6 3. =$B$5 Answer: 3. Dollar signs lock the column and row for copied formulas. Microsoft guidance: https://support.microsoft.com/en-us/excel/switch-between-relative-absolute-and-mixed-references --- Link sheets inside one workbook (10 minutes) Use the Settings rate to calculate a checked adjusted amount. LEARN A shared input belongs in one labelled place. Sheet references let other calculations use it. EXAMPLE Settings!B2 is 10%. Summary!B5 is £200. In Summary!B6 enter =Settings!B2. Format B6 as Percentage. In Summary!B7 enter =B5*(1+B6). The result is £220. STEPS 1. Select Summary!B6 and type =. 2. Select the Settings tab, then B2, and press Enter. 3. Check the formula bar reads =Settings!B2. 4. Enter the amount formula in Summary!B7. 5. Change Settings!B2 to 12%. Check £224, then restore 10% and £220. TRY IT In spare General-format cells, compare =200*(1+10) and =200*(1+10%). Explain why a stored rate of 10 (1000%) differs from 10% (0.1). CHECK YOUR RESULT The linked rate is 10% (stored as 0.1); the explicit test with 10 gives £2,200. £200 with a 10% uplift gives £220. The 12% test gives £224 and the original rate is restored. IF IT DOES NOT WORK A sheet name containing spaces needs quotes, for example ='Training Rates'!B2. Let Excel build the reference by selecting the source cell. OPTIONAL CHECK Which result matches a 10% increase on £200? 1. £20 2. £220 3. £2,200 Answer: 2. The increase is £20. The adjusted total is £200 + £20 = £220. Microsoft guidance: https://support.microsoft.com/en-us/excel/switch-between-relative-absolute-and-mixed-references --- Link a value from another file (15 minutes) Create one deliberate external link between your two fictional practice files. LEARN External links depend on another file and access to it. A displayed value can be cached rather than current. EXAMPLE Use desktop Excel for this local-file exercise. Save both downloads in a practice folder. Do not use live workbooks. With both files open, Summary!B6 can refer to Rates!B2 in excel-intermediate-source.xlsx. Excel may show ='[excel-intermediate-source.xlsx]Rates'!$B$2 or a longer saved path. Do not type a guessed path. STEPS 1. Open copies of both files in desktop Excel. On the programme workbook’s Summary sheet, check that B5 contains 200. 2. In B7, enter =B5*(1+B6) if the formula is missing. B7 will show the adjusted amount. 3. In B6, type =. Switch to the source workbook, select B2 on the Rates sheet and press Enter. Check that B6 shows 10% and B7 shows £220. 4. Check that the formula in B6 names your source file. In the source copy, change the rate to 12%. Check B6 shows 12% and B7 shows £224. 5. Restore the source rate to 10%. Check B7 returns to £220, then save both practice copies. TRY IT Close and reopen your destination practice copy. Inspect the source and explain what you would verify before refreshing any external link. CHECK YOUR RESULT Only the provided synthetic source file is linked. You checked a known input change and restored it. You can identify the source file and cell from the formula. IF IT DOES NOT WORK Do not enable unknown links, alter security settings or upload files to a work account for this exercise. Excel for the web needs supported online files; do not upload these for this lesson. If local-file linking is unavailable or blocked, read the example and continue with the same-workbook version. OPTIONAL CHECK What does a displayed linked value prove by itself? 1. That the source is definitely current 2. Only that a value is displayed; freshness still needs a check 3. That the source file can be deleted Answer: 2. Excel can display cached data. Check source identity, availability and the last successful refresh. Microsoft guidance: https://support.microsoft.com/en-us/excel/create-workbook-links --- Inspect, repair and safely remove a link (15 minutes) Decide whether to refresh, change a source or keep a value-only snapshot. LEARN A broken link is a source problem, not permission to choose a similar-looking file. Breaking links replaces formulas with values. EXAMPLE In current Windows Excel, Data > Queries & Connections > Workbook Links opens the link pane. Older versions may show Edit Links. The intended source is your fictional excel-intermediate-source.xlsx, Rates!B2. A value-only copy can keep 10% and £220, but it will no longer follow future source changes. STEPS 1. Inspect the workbook-link pane in your practice copy. Confirm the exact source. 2. If a source has moved, locate the verified original before using Change Source. Do not guess. 3. Refresh only a known, trusted source allowed by your organisation. 4. For a removal exercise, first save another copy named value-only-practice.xlsx. 5. Break only the synthetic workbook link in that extra copy. Read the warning. Check that B6 is a value, B7 still gives £220 and the source link is absent. TRY IT Write a three-line handover: source file and cell, the checked rate, and whether the result remains linked or is a dated snapshot. CHECK YOUR RESULT Linked and value-only copies are clearly distinguished. The original linked practice copy is retained. You understand a snapshot will not refresh. IF IT DOES NOT WORK Do not rely on Undo to recover a broken workbook link. Keep an untouched copy first. If no link pane exists, use the worked example instead of changing security settings. OPTIONAL CHECK What is the main consequence of Break Links? 1. It repairs the source automatically 2. It deletes the whole workbook 3. It replaces linked formulas with their current values Answer: 3. Breaking removes the dependency. Preserve a separate linked copy before doing this. Microsoft guidance: https://support.microsoft.com/en-us/excel/manage-workbook-links --- SESSION 4: Charts (15:30–16:15) Choose a chart for the question (15 minutes) Create a clear comparison of the three team costs. LEARN Choose the question first. Categories need a comparison; a time trend needs a time axis. EXAMPLE Charts!A1:B4 contains checked totals: North £250, South £200, West £90. A plain 2-D column or bar chart suits this comparison. A line chart would suggest an ordered trend that these team names do not represent. STEPS 1. Check the chart table sums to £540. 2. Select Charts!A1:B4, including headings and excluding any total row. 3. Choose Insert > Column or Bar Chart, then a plain 2-D chart. 4. Check three categories and all three plotted values. 5. Keep the source table alongside the chart. TRY IT Explain when a line chart would be more appropriate. Use a fictional month-by-month example without changing this chart’s team data. CHECK YOUR RESULT There are three team categories, not a fourth Grand total bar. The values match 250, 200 and 90. You can explain your chart choice. IF IT DOES NOT WORK If Excel plots the wrong series, inspect Select Data and the selected range. Do not change source values to make the chart look better. OPTIONAL CHECK Which question fits a line chart better? 1. How did cost change month by month? 2. Which of three teams costs most? 3. Which order has priority High? Answer: 1. A line chart can show change over an ordered time series. A bar or column chart is clearer for separate teams. Microsoft guidance: https://support.microsoft.com/en-us/excel/creating-charts-from-start-to-finish --- Label the chart so someone else can read it (15 minutes) Add an informative title, currency units and readable labels. LEARN A chart without a scope or units can tell the wrong story even when its bars are correct. EXAMPLE Title: Fictional training orders: cost by team. Value axis: Cost (£). Categories: North, South, West. Values: £250, £200, £90. For bars or columns, start the value axis at zero. STEPS 1. Select the chart. Open Chart Design > Add Chart Element where available. 2. Add the title and value-axis title. 3. Check all category labels remain visible. 4. Add data labels if they improve readability. A single-series legend may be unnecessary. 5. Inspect the scale and ensure the value axis starts at zero. TRY IT Ask a colleague, or imagine a new reader: can they identify what is measured, the units and the largest team without looking elsewhere? CHECK YOUR RESULT The title says these are fictional training orders. Pounds and all team names are visible. The zero baseline and values support a fair comparison. IF IT DOES NOT WORK Commands vary by Excel version; use Chart Design or the chart controls available. If labels overlap, enlarge the chart or use a horizontal bar chart. OPTIONAL CHECK Which title is most useful? 1. Chart 1 2. Fictional training orders: cost by team 3. Business performance is excellent Answer: 2. A descriptive title states the subject. It does not claim conclusions beyond the data. Microsoft guidance: https://support.microsoft.com/en-us/excel/creating-charts-from-start-to-finish --- Improve the design without changing the message (15 minutes) Keep a readable chart and a short, evidence-based explanation. LEARN Design should make the evidence easier to read, not exaggerate differences or hide uncertainty. EXAMPLE Use a calm, high-contrast style, readable text and a simple 2-D layout. North has the highest cost at £250; total cost is £540. This does not prove North is inefficient. Avoid 3-D effects, decorative backgrounds and colour-only meaning. STEPS 1. Save your finished practice copy. 2. Try one chart style and inspect text contrast and labels. 3. Check the source range and values again after the style change. 4. Review at a normal screen size and in File > Print preview. Do not print unless you need to. 5. Write one supported observation and one limit of the data. TRY IT Keep your chart and a two-sentence handover: what the data shows, and what it cannot establish. Reconcile the chart back to the eight source orders. CHECK YOUR RESULT The chart remains 250, 200, 90 with total £540. The design is readable without relying on colour. Your interpretation does not turn cost differences into unproven performance claims. IF IT DOES NOT WORK If a style hides labels or adds clutter, revert to a simple 2-D style. If the numbers changed, stop and inspect the source before formatting further. OPTIONAL CHECK What is supported by these figures? 1. North costs most in this fictional list 2. North is definitely inefficient 3. West has fewer employees Answer: 1. The data supports a cost comparison only. It does not provide efficiency or staffing evidence. Microsoft guidance: https://support.microsoft.com/en-us/excel/creating-charts-from-start-to-finish --- FINAL PRACTICAL CHECK Eight records, total £540; six Open orders. North £250, South £200, West £90. Open cost £400. Only TR-101 meets the £100 rule. Label the fixed reporting date (3 November 2026), rate source and link status. 10% on £200 gives £220. Chart three teams with pounds, labels and a zero baseline. Keep one supported observation and note what the data cannot prove. EVALUATION What can you now do unaided? What needs another try? What will you check before using approved work data? Keep your workbook and handover notes; no evaluation is submitted.