IBEDC Energy Analysis in Excel 2019

Import, clean, and analyze Nigerian energy distribution data using Excel 2019.

Introduction

30 Second Summary

Electricity distribution companies serve millions of customers across dozens of regions, generating mountains of billing records, feeder data, and collection reports every month. Turning that raw data into actionable insights is exactly what data analysts get hired to do.

In this project, you will build a complete energy distribution analysis workbook for IBEDC (Ibadan Electricity Distribution Company) using Excel 2019. You will import messy CSV data with Power Query, clean and transform it, then analyze performance across five regions using advanced lookup and aggregation formulas.

What You'll Build

A multi-sheet Excel workbook that imports raw IBEDC operational data, connects it across sheets with lookup formulas, aggregates regional performance metrics, and scores field officers with conditional logic and formatting.

By the end of this project, you'll have:

  • A Power Query connection that imports and cleans raw CSV billing and feeder data, removing duplicates, trimming whitespace, splitting columns, and changing data types.
  • A cross-sheet analysis system using VLOOKUP, INDEX/MATCH, and multi-criteria formulas (SUMIFS, COUNTIFS, AVERAGEIFS) to analyze energy distribution across IBEDC's five regions.
  • A Field Officer Scorecard combining IFS logic, error handling, text and date functions, and conditional formatting that mirrors real employer skills assessments.
  • Secret Mission: Build an interactive PivotTable dashboard with slicers for filtering IBEDC energy data by region and tariff band.

Are there any prerequisites?

You should be comfortable with basic Excel tasks like entering data, writing simple formulas (SUM, AVERAGE), and navigating between sheets. You also need Excel 2019 installed on Windows with access to the Data tab's Get & Transform Data group.

Before We Start

Before We Start...

Set Up Your Workbook and Sample Data

Every data analysis project starts with the data itself. In this step, you will create the workbook that holds your entire IBEDC analysis and prepare the raw data files that simulate real-world operational records.

Real operational data is rarely clean. You will create two CSV files with intentional messiness (extra spaces, combined columns, duplicate rows, mixed date formats) so you can practice cleaning them with Power Query in the next step.

In this step, get ready to:
  • Create and save your analysis workbook.
  • Build two CSV sample data files with realistic IBEDC billing and feeder records.
  • Set up a FieldOfficers sheet with 10 field officers across IBEDC's five regions.
Create and save your workbook

You need a dedicated workbook to hold all your analysis sheets. Saving it now gives you a stable home for everything you build in this project.

  • In Excel, click File in the top left corner.
  • Click New, then select Blank workbook.
  • Click File again, then click Save As.
  • Navigate to your Desktop.
  • In the File name field, type IBEDC_Distribution_Analysis.
  • Make sure the Save as type dropdown shows Excel Workbook (*.xlsx).
  • Click Save.

Your title bar now shows IBEDC_Distribution_Analysis.xlsx. This confirms the workbook is saved.

Don't see .xlsx in the title bar?

Make sure you selected Excel Workbook (*.xlsx) from the Save as type dropdown, not a CSV or older format. If you accidentally saved as something else, use File > Save As again and choose the correct type.

Help me fix my workbook file format.

Create the billing records CSV file

This CSV simulates messy operational billing data from IBEDC's business hubs. It has intentional problems: extra spaces in hub names, a combined Hub-Region column that needs splitting, duplicate rows, and mixed date formats. You will clean all of this with Power Query in Step 2.

  • Press the Windows key to open your search bar.
  • Type Notepad and press Enter to open it.
  • Copy and paste the entire block of text below into Notepad:
RecordID,CustomerID,Hub-Region,TariffBand,EnergyBilled_kWh,AmountBilled_NGN,AmountPaid_NGN,MeterType,BillingMonth
1,CUST001, Molete-Ibadan,A,450,18000,18000,Prepaid,01/15/2024
2,CUST002,Dugbe-Ibadan,B,320,11200,9500,Postpaid,01/15/2024
3,CUST003,Osogbo-Osun,A,510,20400,20400,Prepaid,2024-01-15
4,CUST004,Ota-Ogun,C,280,8400,7000,Unmetered,15-Jan-2024
5,CUST005,Ojoo-Ibadan,B,390,13650,13650,Prepaid,01/15/2024
6,CUST006, Omu-Aran-Kwara,D,180,4500,3200,Postpaid,2024-01-15
7,CUST007,Ijebu Ode-Ogun,A,620,24800,22000,Prepaid,01/15/2024
8,CUST008,Ogbomosho-Oyo,C,250,7500,7500,Prepaid,15-Jan-2024
9,CUST009,Ede-Osun,B,340,11900,10000,Postpaid,2024-01-15
10,CUST010,Akanran-Ibadan,E,150,3000,2800,Unmetered,01/15/2024
11,CUST011,Molete-Ibadan,A,480,19200,19200,Prepaid,2024-01-15
12,CUST012, Dugbe -Ibadan,B,310,10850,8900,Postpaid,15-Jan-2024
13,CUST013,Osogbo-Osun,C,270,8100,8100,Prepaid,01/15/2024
14,CUST014,Ota-Ogun,A,530,21200,19000,Postpaid,2024-01-15
15,CUST015,Ojoo-Ibadan,D,200,5000,4200,Unmetered,01/15/2024
16,CUST016,Omu-Aran-Kwara,B,350,12250,12250,Prepaid,15-Jan-2024
17,CUST017,Ijebu Ode-Ogun,C,290,8700,7500,Postpaid,2024-01-15
18,CUST018, Ogbomosho-Oyo,E,140,2800,2000,Unmetered,01/15/2024
19,CUST019,Ede-Osun,A,470,18800,18800,Prepaid,2024-01-15
20,CUST020,Akanran-Ibadan,B,330,11550,10000,Postpaid,15-Jan-2024
21,CUST021,Molete-Ibadan,C,260,7800,7800,Prepaid,01/15/2024
22,CUST014,Ota-Ogun,A,530,21200,19000,Postpaid,2024-01-15
23,CUST022,Dugbe-Ibadan,A,490,19600,17500,Prepaid,02/15/2024
24,CUST023, Osogbo- Osun,B,360,12600,12600,Postpaid,2024-02-15
25,CUST024,Ota-Ogun,D,190,4750,3800,Unmetered,15-Feb-2024
26,CUST025,Ojoo-Ibadan,A,520,20800,20800,Prepaid,02/15/2024
27,CUST026,Omu-Aran-Kwara,C,275,8250,7000,Postpaid,2024-02-15
28,CUST027,Ijebu Ode-Ogun,B,345,12075,12075,Prepaid,15-Feb-2024
29,CUST028,Ogbomosho-Oyo,A,500,20000,18500,Postpaid,02/15/2024
30,CUST029, Ede-Osun,E,160,3200,2500,Unmetered,2024-02-15
31,CUST030,Akanran-Ibadan,B,335,11725,11725,Prepaid,15-Feb-2024
32,CUST031,Molete-Ibadan,D,210,5250,4500,Postpaid,02/15/2024
33,CUST032,Dugbe-Ibadan,A,540,21600,21600,Prepaid,2024-02-15
34,CUST033,Osogbo-Osun,C,285,8550,7200,Unmetered,15-Feb-2024
35,CUST034,Ota-Ogun,B,370,12950,12950,Prepaid,02/15/2024
36,CUST035, Ojoo -Ibadan,A,460,18400,16000,Postpaid,2024-02-15
37,CUST036,Omu-Aran-Kwara,E,130,2600,2600,Prepaid,15-Feb-2024
38,CUST037,Ijebu Ode-Ogun,A,580,23200,21000,Postpaid,02/15/2024
39,CUST038,Ogbomosho-Oyo,B,325,11375,9800,Unmetered,2024-02-15
40,CUST039,Ede-Osun,C,295,8850,8850,Prepaid,15-Feb-2024
41,CUST040,Akanran-Ibadan,A,475,19000,19000,Prepaid,02/15/2024
42,CUST041, Molete-Ibadan,D,195,4875,4000,Postpaid,2024-02-15
43,CUST042,Dugbe-Ibadan,B,355,12425,11000,Prepaid,15-Feb-2024
44,CUST033,Osogbo-Osun,C,285,8550,7200,Unmetered,15-Feb-2024
45,CUST043,Ota-Ogun,A,545,21800,21800,Prepaid,03/15/2024
46,CUST044,Ojoo-Ibadan,C,265,7950,6500,Postpaid,2024-03-15
47,CUST045,Omu-Aran-Kwara,B,340,11900,11900,Prepaid,15-Mar-2024
48,CUST046, Ijebu Ode-Ogun,A,600,24000,22500,Postpaid,03/15/2024
49,CUST047,Ogbomosho-Oyo,D,175,4375,3500,Unmetered,2024-03-15
50,CUST048,Ede-Osun,A,485,19400,19400,Prepaid,15-Mar-2024
51,CUST049,Akanran-Ibadan,C,255,7650,7650,Prepaid,03/15/2024
52,CUST050,Molete-Ibadan,B,365,12775,11500,Postpaid,2024-03-15
53,CUST051,Dugbe-Ibadan,A,525,21000,21000,Prepaid,15-Mar-2024
54,CUST052, Osogbo-Osun,E,145,2900,2200,Unmetered,03/15/2024
55,CUST053,Ota-Ogun,B,380,13300,13300,Prepaid,2024-03-15
56,CUST054,Ojoo-Ibadan,A,505,20200,18000,Postpaid,15-Mar-2024
57,CUST055,Omu-Aran-Kwara,C,270,8100,7000,Prepaid,03/15/2024
58,CUST056,Ijebu Ode-Ogun,B,350,12250,10500,Postpaid,2024-03-15
59,CUST057,Ogbomosho-Oyo,A,515,20600,20600,Prepaid,15-Mar-2024
60,CUST058, Ede -Osun,D,185,4625,3800,Unmetered,03/15/2024
61,CUST059,Akanran-Ibadan,A,495,19800,19800,Prepaid,2024-03-15
62,CUST060,Molete-Ibadan,C,245,7350,6000,Postpaid,15-Mar-2024
63,CUST061,Dugbe-Ibadan,B,375,13125,13125,Prepaid,03/15/2024
64,CUST052, Osogbo-Osun,E,145,2900,2200,Unmetered,03/15/2024
65,CUST062,Ota-Ogun,A,555,22200,20000,Postpaid,2024-03-15
66,CUST063,Ojoo-Ibadan,D,205,5125,4300,Prepaid,15-Mar-2024

What is this data?

This CSV represents monthly billing records from IBEDC's business hubs. Each row is one customer's billing record for a specific month.

The data has intentional problems you will fix with Power Query later. Extra spaces appear in hub names (like Molete-Ibadan). The Hub-Region column combines two pieces of information (hub and region) with a hyphen. Some rows are duplicated (rows 14 and 22 are identical). Date formats vary between MM/DD/YYYY, YYYY-MM-DD, and DD-Mon-YYYY.

  • In Notepad, click File, then click Save As.
  • Navigate to your Desktop.
  • In the File name field, type billing_records.csv.
  • Change the Save as type dropdown from "Text Documents (*.txt)" to All Files (*.*).
  • Make sure the Encoding dropdown at the bottom shows UTF-8.
  • Click Save.

Check your Desktop. You should see billing_records.csv listed as a file.

File saved as .txt instead of .csv?

If you see billing_records.csv.txt on your Desktop, you forgot to change the Save as type dropdown to All Files (*.*). Delete the file, return to Notepad, and save again with the correct settings.

Help me save my CSV file correctly in Notepad.

Now create the second CSV file for feeder data. Clear the current content in Notepad or open a new Notepad window.

  • In Notepad, press Ctrl+A to select all text, then press Delete to clear it.
  • Copy and paste the entire block of text below into Notepad:
FeederID,FeederName,VoltageLevel,Region,BusinessHub,Capacity_MVA,TariffBand
FDR001,Molete Injection,33kV,Ibadan,Molete,15.0,A
FDR002,Dugbe Industrial,33kV,Ibadan,Dugbe,20.0,A
FDR003,Ojoo Residential,11kV,Ibadan,Ojoo,7.5,B
FDR004,Akanran Rural,11kV,Ibadan,Akanran,5.0,D
FDR005,Osogbo Main,33kV,Osun,Osogbo,15.0,A
FDR006,Ota Industrial,33kV,Ogun,Ota,20.0,A
FDR007,Ijebu Ode Town,33kV,Ogun,Ijebu Ode,15.0,B
FDR008,Ogbomosho Central,11kV,Oyo,Ogbomosho,7.5,C
FDR009,Omu-Aran Feeder,11kV,Kwara,Omu-Aran,5.0,D
FDR010,Ede Distribution,33kV,Osun,Ede,10.0,B
FDR011,Molete Residential,11kV,Ibadan,Molete,7.5,B
FDR012,Dugbe Commercial,11kV,Ibadan,Dugbe,10.0,A
FDR013,Osogbo Industrial,33kV,Osun,Osogbo,20.0,A
FDR014,Ota Residential,11kV,Ogun,Ota,7.5,C
FDR015,Omu-Aran Rural,11kV,Kwara,Omu-Aran,5.0,E

What is this feeder data?

Feeders are the physical power lines that distribute electricity from substations to neighborhoods. IBEDC operates 33kV (high voltage) and 11kV (medium voltage) feeders across its network.

This catalog maps each feeder to its region, business hub, capacity, and tariff band. You will use it as a lookup reference in Step 3 to connect billing records to feeder details.

  • Click File, then Save As.
  • Navigate to your Desktop.
  • In the File name field, type feeders.csv.
  • Set Save as type to All Files (*.*).
  • Confirm the Encoding dropdown shows UTF-8.
  • Click Save.

Check your Desktop again. You should now see both billing_records.csv and feeders.csv alongside your workbook file.

Create the FieldOfficers sheet in your workbook

Field officers are responsible for revenue collection in each business hub. This sheet provides the reference data you will use in Step 5 to build the performance scorecard.

  • Switch back to your IBEDC_Distribution_Analysis.xlsx workbook in Excel.
  • Right-click the Sheet1 tab at the bottom of the screen.
  • Click Rename and type FieldOfficers, then press Enter.
  • In cell A1, type the column headers exactly as shown in the table below, one per cell across row 1.
  • Enter the 10 rows of officer data starting from row 2:
OfficerID | FullName              | Region  | BusinessHub  | HireDate   | MonthlyCollectionTarget_NGN
OFF001    | Adebayo Ogunlade      | Ibadan  | Molete       | 03/12/2019 | 2500000
OFF002    | Funke Adeyemi         | Ibadan  | Dugbe        | 07/05/2020 | 3000000
OFF003    | Chinedu Okafor        | Ibadan  | Ojoo         | 11/20/2018 | 2200000
OFF004    | Amina Bello           | Ibadan  | Akanran      | 02/15/2021 | 1800000
OFF005    | Taiwo Oladipo         | Osun    | Osogbo       | 06/08/2019 | 2800000
OFF006    | Ngozi Eze             | Ogun    | Ota          | 09/14/2020 | 3200000
OFF007    | Babatunde Afolabi     | Ogun    | Ijebu Ode    | 01/22/2019 | 2600000
OFF008    | Halima Abdullahi      | Oyo     | Ogbomosho    | 04/30/2021 | 2000000
OFF009    | Oluwaseun Fakoya      | Kwara   | Omu-Aran     | 08/18/2020 | 1500000
OFF010    | Bukola Oyelaran       | Osun    | Ede          | 05/03/2019 | 2400000

How do I enter this data?

Type each column header (OfficerID, FullName, Region, BusinessHub, HireDate, MonthlyCollectionTarget_NGN) in cells A1 through F1. Then type each officer's details row by row starting from A2.

The pipe characters (|) in the table above are just visual separators. Do not type them. Each value goes in its own cell.

  • After entering all 10 rows, select cells A1:F11 (headers plus all data rows).
  • Go to the Insert tab in the ribbon.
  • Click Table.
  • In the Create Table dialog, confirm My table has headers is checked.
  • Click OK.
  • Press Ctrl+S to save your workbook.

Why format this as a table?

Excel tables automatically expand when you add data. They also enable structured references in formulas (like FieldOfficers[Region] instead of C:C). This makes the SUMIFS and VLOOKUP formulas you write later much easier to read.

Your sheet should now display the data with alternating row colors and filter arrows in the header row. This confirms Excel recognized it as a table.

Don't see filter arrows or alternating colors?

Make sure you selected the full range A1:F11 before clicking Table. If you missed the headers, Excel may not have formatted it correctly. Press Ctrl+Z to undo, reselect the range, and try again.

Help me format my FieldOfficers data as a table.

✔️ Awesome, I've got everything!

Great. Double check you have saved your workbook with Ctrl+S.

ⓧ I'd like to double check the full setup

At this point you should have three items on your Desktop:

  • IBEDC_Distribution_Analysis.xlsx with a single sheet named FieldOfficers containing 10 rows of officer data formatted as a table.
  • billing_records.csv with 66 data rows (header row plus 66 records) including extra spaces, duplicate rows, and mixed date formats.
  • feeders.csv with 15 feeder records covering all five IBEDC regions.

If any file is missing, re-read the substep for that file and follow the save instructions again.

Your workbook and sample data files are ready. Next up, you will import these messy CSV files into Excel using Power Query and transform them into clean, structured tables.

Import and Clean Data with Power Query

Your workbook is set up with the FieldOfficers sheet and both CSV files are ready on your machine.

Now it is time to bring that raw data into Excel. The billing records and feeder files both contain messy data: extra spaces, duplicate rows, and columns that need splitting. Power Query (Get & Transform) handles all of this cleaning before the data ever touches your worksheet.

In this step, get ready to:
  • Import and clean the billing records CSV using Power Query.
  • Import and clean the feeders CSV using a second Power Query connection.
  • Load both cleaned queries into separate worksheets and verify the connections.
Import billing_records.csv using Power Query

The billing records file has intentional messiness: extra spaces in the Hub-Region column, duplicate rows, and a combined column that needs splitting into separate BusinessHub and Region columns.

  • Switch back to your IBEDC_Distribution_Analysis.xlsx workbook in Excel.
  • Click the Data tab in the ribbon.
  • Click Get Data in the Get & Transform Data group.
  • Select From File, then From Text/CSV.
  • Browse to and select your billing_records.csv file, then click Import.
  • In the preview window that appears, click Transform Data to open the Power Query Editor.

Why Transform Data instead of Load?

Clicking Load would dump the raw, messy data straight into a worksheet. Clicking Transform Data opens the Power Query Editor where you can clean the data first.

You are now in the Power Query Editor. The first cleaning task is removing duplicate rows.

  • Press Ctrl+A to select all columns.
  • Click the Home tab, then Remove Rows, then Remove Duplicates.

Next, trim the extra spaces from the Hub-Region column.

  • Click the Hub-Region column header to select it.
  • Click the Transform tab in the ribbon.
  • Click Format, then select Trim.

What does Trim do in Power Query?

Trim removes leading and trailing spaces from every value in the selected column. This fixes entries like " Molete-Ibadan " that would otherwise cause lookup mismatches later.

Now split the Hub-Region column into two separate columns using the hyphen as the delimiter.

  • With the Hub-Region column still selected, click the Home tab.
  • Click Split Column, then By Delimiter.
  • In the Select or enter delimiter dropdown, choose Custom.
  • Type a hyphen - in the custom delimiter field.
  • Leave the Split at option set to Each occurrence of the delimiter.
  • Click OK.

Power Query splits the column into two new columns. Rename them to match the project schema.

  • Double-click the first new column header (likely named Hub-Region.1) and rename it to BusinessHub.
  • Double-click the second new column header (likely named Hub-Region.2) and rename it to Region.

Finally, change the data types for three columns so Excel interprets them correctly.

  • Click the BillingMonth column header to select it.
  • In the Home tab, click the Data Type dropdown and select Date.
  • If a Change Column Type dialog appears, select Replace Current.
  • Hold Ctrl and click both the AmountBilled_NGN and AmountPaid_NGN column headers to select them together.
  • Click the Data Type dropdown and select Fixed decimal number (this is the Currency type).
  • If the Change Column Type dialog appears again, select Replace Current.

What are these data type changes doing?

Setting BillingMonth to Date ensures Excel recognizes dates correctly for sorting and filtering.

Setting the amount columns to Fixed decimal number (Currency) ensures calculations treat these as monetary values with consistent decimal precision.

Seeing errors after changing the date type?

If some rows show an error in the BillingMonth column, the source CSV may contain mixed date formats that Power Query cannot parse automatically. Right-click the Changed Type step in the Applied Steps pane on the right and select Delete. Then try setting the column type to Date again. Power Query will use your system locale to interpret dates.

Help me fix the date type error in my Power Query billing records import.

Import feeders.csv using a second Power Query connection

The feeders file is cleaner than the billing records, but it still needs some tidying: blank rows, untrimmed names, and possible invalid voltage levels.

  • While still in the Power Query Editor, click the Home tab, then New Source, then File, then Text/CSV.
  • Browse to and select your feeders.csv file, then click Import.
  • Click OK on the preview dialog to load the data into a new query.

You now have a second query in the Power Query Editor. Start by removing rows with blank FeederID values.

  • Click the dropdown arrow on the FeederID column header.
  • In the filter list, uncheck (Blanks) or null if listed.
  • Click OK to apply the filter. Any rows with blank FeederID values are now excluded.

Now trim the FeederName column to remove extra spaces.

  • Click the FeederName column header to select it.
  • Click the Transform tab, then Format, then Trim.

Change the Capacity_MVA column to the correct numeric type.

  • Click the Capacity_MVA column header.
  • In the Home tab, click the Data Type dropdown and select Decimal Number.
  • If the Change Column Type dialog appears, select Replace Current.

Finally, filter out any rows where VoltageLevel is not a valid value.

  • Click the dropdown arrow on the VoltageLevel column header.
  • In the filter list, uncheck everything except 33kV and 11kV.
  • Click OK to apply the filter. Any rows with invalid voltage levels are now excluded.

Why filter VoltageLevel?

This is a data quality check. IBEDC's network uses 33kV and 11kV feeders. Any row with a different value (blank, typo, or invalid entry) is bad data that would skew your analysis later.

Load both queries into worksheets and verify connections

Both queries are now cleaned in the Power Query Editor. Time to send them to your workbook as structured tables.

  • In the Power Query Editor, click the Home tab.
  • Click Close & Load. Power Query loads both queries into separate worksheets in your workbook.

What does Close & Load do?

Close & Load saves your query transformations and outputs the cleaned results as Excel tables on new worksheets. The query connection stays live, so you can refresh the data any time the source CSV changes.

Verify the results by checking the new sheets and the Queries & Connections pane.

  • Look at the bottom of your workbook. You should see two new sheet tabs named after the source files.
  • Right-click the billing_records sheet tab and select Rename. Type BillingRecords and press Enter.
  • Right-click the feeders sheet tab and select Rename. Type Feeders and press Enter.
  • Click the Data tab, then click the Queries & Connections button in the ribbon to open the pane.
  • Confirm both queries appear in the Queries & Connections pane with row counts next to each.

Check that the BillingRecords sheet has separate BusinessHub and Region columns (no more Hub-Region). Confirm the Feeders sheet shows FeederID, FeederName, VoltageLevel, Region, BusinessHub, Capacity_MVA, and TariffBand columns.

Do not see the Queries & Connections pane?

Click the Data tab, then click the Queries & Connections button in the ribbon to toggle the pane open. It appears on the right side of the workbook window.

Help me find the Queries & Connections pane in Excel 2019.

  • Save your workbook by pressing Ctrl+S.

Your raw CSV data is now clean, typed, and loaded into structured Excel tables. Next up, you will build cross-sheet lookups using VLOOKUP and INDEX/MATCH to connect the billing records to feeder details.

Build Cross-Sheet Lookups with VLOOKUP and INDEX/MATCH

Your BillingRecords and Feeders sheets are now clean, structured tables thanks to Power Query. The next challenge is connecting them.

Each billing record lists a BusinessHub, but it does not tell you which feeder serves that hub or what voltage level it uses. That information lives on a separate sheet. In this step, you will use lookup formulas to pull feeder details across sheets.

In this step, get ready to:
  • Add VLOOKUP columns to BillingRecords that attempt to pull feeder names and voltage levels from the Feeders sheet.
  • Create a Feeder Lookup sheet using INDEX/MATCH to retrieve any feeder detail by FeederID.
  • Wrap all lookup formulas in IFERROR so invalid inputs display a clean message instead of an error.
Add VLOOKUP columns to BillingRecords

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. You will use it to attempt pulling the FeederName for each billing record based on its BusinessHub.

What is VLOOKUP?

VLOOKUP stands for Vertical Lookup. Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

The lookup value must be in the first column of your table_array. The col_index_num tells Excel which column to return, counting from the left. Use FALSE for an exact match.

  • Switch to the BillingRecords sheet tab at the bottom of your workbook.
  • Click the first empty column header to the right of your data (the column after BillingMonth).
  • Type FeederName as the column header and press Enter.
  • In the first data cell of the new FeederName column, enter this formula:
=IFERROR(VLOOKUP([@BusinessHub],Feeders!E:B,-4,FALSE),"")

What does this formula do?

This formula uses [@BusinessHub] to grab the current row's BusinessHub value. The @ symbol is a structured reference that means "same row" inside an Excel table.

The IFERROR wrapper catches any lookup failures and displays an empty string instead of an error value. This keeps your table clean even when the VLOOKUP cannot find a match.

  • Press Enter to confirm the formula. Excel auto-fills it down through the entire table column.
  • Click the next empty column header to the right and type VoltageLevel as the header.
  • In the first data cell of the VoltageLevel column, enter a second VLOOKUP following the same pattern but targeting the VoltageLevel column from the Feeders sheet.
  • Press Enter to confirm.
  • Save your workbook by pressing Ctrl+S.
  • Scroll through the BillingRecords table. You will see empty cells in the FeederName and VoltageLevel columns.

Why are the VLOOKUP results empty?

VLOOKUP can only look left to right. The lookup column must be the leftmost column in your table_array, and the return column must be to its right.

In the Feeders table, BusinessHub sits in column E while FeederName is in column B (to the left). VLOOKUP cannot retrieve values from a column to the left of the lookup column. The IFERROR wrapper catches the resulting error and returns an empty string.

This is exactly why INDEX/MATCH exists. It can look up in any column and return from any other column, regardless of position.

Seeing a formula error instead of an empty cell?

If you see a formula syntax error, make sure you typed the IFERROR wrapper correctly. The formula should have two opening and two closing parentheses total.

If you see #VALUE! without IFERROR catching it, double-check that the IFERROR function wraps the entire VLOOKUP expression.

Help me troubleshoot my VLOOKUP formula in the BillingRecords sheet.

Create the Feeder Lookup sheet with INDEX/MATCH

Now you will build a dedicated lookup tool that retrieves any feeder's details by its FeederID. This approach uses INDEX/MATCH, which can pull values from any column regardless of its position relative to the lookup column.

Why INDEX/MATCH instead of VLOOKUP?

You just saw VLOOKUP's limitation firsthand. It requires the return column to be to the right of the lookup column. Rearranging your data to satisfy this requirement is impractical in real workbooks.

INDEX/MATCH has no direction restriction. MATCH finds the row number of your lookup value, and INDEX returns any column's value at that row. Employers test INDEX/MATCH in hiring assessments because it demonstrates deeper formula understanding.

  • Right-click any sheet tab at the bottom of your workbook and select Insert.
  • Choose Worksheet and click OK.
  • Double-click the new sheet tab and rename it to Feeder Lookup.
  • In cell A1, type FeederID as a label.
  • In cell A2, type a valid FeederID from your Feeders table (for example, the first FeederID in your data).

Now set up labels in row 1 for the values you want to retrieve.

  • In cell B1, type FeederName.
  • In cell C1, type VoltageLevel.
  • In cell D1, type Region.
  • In cell E1, type Capacity_MVA.
  • In cell F1, type TariffBand.
  • In cell B2, enter this INDEX/MATCH formula to retrieve FeederName:
=INDEX(Feeders!B:B,MATCH(A2,Feeders!A:A,0))

What does this formula do?

MATCH(A2,Feeders!A:A,0) searches the FeederID column (column A) on the Feeders sheet for the value you typed in A2. The 0 means exact match. It returns the row number where the match is found.

INDEX(Feeders!B:B,...) then goes to the Feeders sheet's column B (FeederName) and returns the value at that row number. Together, they look up in column A and return from column B, regardless of column order.

  • In cell C2, enter the same pattern but change the return column to Feeders!C:C for VoltageLevel:
=INDEX(Feeders!C:C,MATCH(A2,Feeders!A:A,0))
  • In cell D2, use Feeders!D:D to return Region:
=INDEX(Feeders!D:D,MATCH(A2,Feeders!A:A,0))
  • In cell E2, use Feeders!F:F to return Capacity_MVA:
=INDEX(Feeders!F:F,MATCH(A2,Feeders!A:A,0))
  • In cell F2, use Feeders!G:G to return TariffBand:
=INDEX(Feeders!G:G,MATCH(A2,Feeders!A:A,0))
  • Press Enter after each formula.
  • Check that cells B2 through F2 now display the correct details for the FeederID you entered in A2.

Seeing #N/A in your results?

A #N/A error means MATCH could not find your FeederID value in the Feeders sheet's column A. Double-check that the value in A2 matches a FeederID on the Feeders sheet exactly, with no extra spaces or different capitalization.

Help me fix the #N/A error in my INDEX/MATCH formula on the Feeder Lookup sheet.

Wrap formulas with IFERROR for clean error handling

If someone types an invalid FeederID in cell A2, your INDEX/MATCH formulas will show a #N/A error. Wrapping each formula in IFERROR lets you display a friendly message instead.

  • Click cell B2 on the Feeder Lookup sheet.
  • In the formula bar, update the formula to wrap it in IFERROR:
=IFERROR(INDEX(Feeders!B:B,MATCH(A2,Feeders!A:A,0)),"Feeder Not Found")

What does IFERROR do?

IFERROR checks whether the formula inside it produces an error. If it does, Excel returns the value you specify (in this case, the text "Feeder Not Found"). If the formula works normally, the result passes through unchanged.

  • Press Enter.
  • Repeat for cells C2, D2, E2, and F2, wrapping each INDEX/MATCH formula in the same IFERROR pattern with "Feeder Not Found" as the fallback value.
  • Test the error handling by typing an invalid FeederID (like FAKE123) into cell A2.
  • Confirm that all cells in row 2 now display Feeder Not Found instead of #N/A.
  • Replace the invalid ID with a real FeederID from your data to verify the formulas return correct values again.
  • Save your workbook by pressing Ctrl+S.

Still seeing errors after adding IFERROR?

If you see a formula syntax error, make sure the opening parenthesis of IFERROR comes right after the equals sign. The closing parenthesis should wrap around both the INDEX formula and the fallback text.

Count your parentheses. The complete formula should have three opening and three closing parentheses total.

Help me fix my IFERROR formula syntax on the Feeder Lookup sheet.

✔️ Awesome, I've got everything!

Great. Double check you have saved your workbook with the new Feeder Lookup sheet and the updated BillingRecords columns.

ⓧ I'd like to double check the full formulas

Here is a summary of all formulas you should have at this point.

BillingRecords sheet, FeederName column (first data cell):

=IFERROR(VLOOKUP([@BusinessHub],Feeders!E:B,-4,FALSE),"")

BillingRecords sheet, VoltageLevel column (first data cell):

A second VLOOKUP following the same pattern, targeting the VoltageLevel column from the Feeders sheet.

Feeder Lookup sheet, cells B2 through F2:

B2: =IFERROR(INDEX(Feeders!B:B,MATCH(A2,Feeders!A:A,0)),"Feeder Not Found")
C2: =IFERROR(INDEX(Feeders!C:C,MATCH(A2,Feeders!A:A,0)),"Feeder Not Found")
D2: =IFERROR(INDEX(Feeders!D:D,MATCH(A2,Feeders!A:A,0)),"Feeder Not Found")
E2: =IFERROR(INDEX(Feeders!F:F,MATCH(A2,Feeders!A:A,0)),"Feeder Not Found")
F2: =IFERROR(INDEX(Feeders!G:G,MATCH(A2,Feeders!A:A,0)),"Feeder Not Found")

Feeders sheet column layout for reference:

  • Column A: FeederID (the lookup column for MATCH).
  • Column B: FeederName.
  • Column C: VoltageLevel.
  • Column D: Region.
  • Column E: BusinessHub.
  • Column F: Capacity_MVA.
  • Column G: TariffBand.

Your workbook now connects billing records to feeder details across sheets. Next up, you will use multi-criteria formulas like SUMIFS and COUNTIFS to analyze energy distribution across IBEDC's five regions.

Analyze Energy Distribution with SUMIFS, COUNTIFS, and AVERAGEIFS

Your cross-sheet lookups now connect billing records to feeder details automatically. The next challenge is answering management-level questions about IBEDC's energy distribution operations.

Questions like "How much energy did each region distribute?" or "Which region has the best collection rate?" require formulas that aggregate data based on specific conditions. In this step, you will build a Regional Analysis sheet using SUMIFS, COUNTIFS, and AVERAGEIFS to slice IBEDC's billing data by region, tariff band, and meter type.

In this step, get ready to:
  • Build a region-by-region summary table with energy totals, revenue collection rates, and record counts.
  • Create a cross-tab analyzing energy distribution by tariff band for each region.
  • Add a metering status breakdown showing prepaid, postpaid, and unmetered customer counts per region.
Create the regional summary table with SUMIFS

Each of IBEDC's five regions (Ibadan, Oyo, Osun, Ogun, Kwara) needs its own row showing total energy billed, total revenue billed, total revenue paid, and a collection rate. You will use SUMIFS to sum values from the BillingRecords table where the region matches a specific name.

  • Right-click any sheet tab at the bottom of your workbook.
  • Select Insert, then choose Worksheet.
  • Double-click the new tab and rename it to Regional Analysis.
  • Set up your header row by typing the following labels into cells A1 through G1:
A1: Region
B1: Total Energy (kWh)
C1: Total Billed (NGN)
D1: Total Paid (NGN)
E1: Collection Rate %
F1: Record Count
G1: Avg Bill (NGN)
  • Type the five region names into cells A2 through A6:
A2: Ibadan
A3: Oyo
A4: Osun
A5: Ogun
A6: Kwara
  • Enter the following SUMIFS formula in cell B2 to calculate total energy billed for the Ibadan region:
=SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan")

What does SUMIFS do?

SUMIFS adds up all values in a column (like EnergyBilled_kWh) but only for rows where a corresponding column (like Region) matches your criteria. The syntax is SUMIFS(sum_range, criteria_range1, criteria1).

You can add up to 127 criteria pairs in a single SUMIFS formula. For now, one criterion (Region) is enough.

  • Enter this SUMIFS formula in cell C2 for total amount billed:
=SUMIFS(BillingRecords[AmountBilled_NGN],BillingRecords[Region],"Ibadan")
  • Enter this SUMIFS formula in cell D2 for total amount paid:
=SUMIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan")
  • Enter the Collection Rate formula in cell E2:
=D2/C2
  • Enter the COUNTIFS formula in cell F2 to count billing records for Ibadan:
=COUNTIFS(BillingRecords[Region],"Ibadan")
  • Enter the AVERAGEIFS formula in cell G2 to calculate the average bill amount for Ibadan:
=AVERAGEIFS(BillingRecords[AmountBilled_NGN],BillingRecords[Region],"Ibadan")

How do COUNTIFS and AVERAGEIFS differ from SUMIFS?

COUNTIFS counts how many rows match your criteria instead of summing values. AVERAGEIFS calculates the average of a column for rows that match. All three use the same criteria syntax.

The Collection Rate formula divides total paid by total billed. A result of 0.75 means 75% of billed revenue was collected in that region.

  • Replicate the formulas for the remaining four regions. Select cells B2 through G2 and copy them.
  • Paste into rows 3 through 6.
  • Update the region name in each row. Change "Ibadan" to "Oyo" in row 3.
  • Change to "Osun" in row 4.
  • Change to "Ogun" in row 5.
  • Change to "Kwara" in row 6.
  • Format column E as a percentage. Select cells E2:E6.
  • Click Home in the ribbon.
  • Open the Number Format dropdown and select Percentage.
  • Save your workbook with Ctrl+S.

All five regions should show numeric values in columns B through G. No cell should display a #VALUE! or #DIV/0! error.

Seeing #VALUE! or zeros in your formulas?

  • Make sure the table on your BillingRecords sheet is actually named BillingRecords. Click any cell in that table, then check the Table Design tab for the table name.
  • Confirm the column header is exactly Region with no trailing spaces. The structured reference must match the header character-for-character.
  • If you see #DIV/0! in the Collection Rate column, it means Total Billed is zero for that region. Double-check that your region name in the formula matches the data exactly.

help me troubleshoot my SUMIFS formulas on the Regional Analysis sheet

Add a tariff band cross-tab with two-criteria SUMIFS

IBEDC classifies customers into tariff bands A through E based on hours of electricity supply. A cross-tab lets you see how energy distribution breaks down by both region and tariff band simultaneously.

  • Leave a blank row below your summary table (row 8), then type a section header in cell A9:
A9: Energy by Region and Tariff Band (kWh)
  • Set up the cross-tab headers. Type the tariff band letters in B9 through F9:
B9: A
C9: B
D9: C
E9: D
F9: E
  • Type the region names in cells A10 through A14:
A10: Ibadan
A11: Oyo
A12: Osun
A13: Ogun
A14: Kwara
  • Enter this two-criteria SUMIFS formula in cell B10 to calculate energy billed for the Ibadan region in Tariff Band A:
=SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan",BillingRecords[TariffBand],"A")

How does two-criteria SUMIFS work?

Adding a second criteria_range and criteria pair tells SUMIFS to only include rows that match BOTH conditions. In this formula, a row must have Region = "Ibadan" AND TariffBand = "A" for its kWh value to count.

You can chain up to 127 criteria pairs in a single formula. Two criteria is all you need for this cross-tab.

  • Fill in formulas for the remaining cells in the cross-tab (B10 through F14). Each formula uses the same pattern but swaps the region name and tariff band letter.
  • For example, cell C11 (Oyo region, Tariff Band B) would be:
=SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Oyo",BillingRecords[TariffBand],"B")
  • Save your workbook with Ctrl+S.

Your cross-tab should show numeric values in each cell. Some cells may show 0 if no records exist for that region and tariff combination.

All cells showing zero?

  • Check that the tariff band values in your criteria match the data exactly. Your BillingRecords table should contain single letters like A, B, C, D, E in the TariffBand column.
  • Confirm you are referencing the correct column name: BillingRecords[TariffBand]. It must match the column header exactly with no spaces.

help me fix my two-criteria SUMIFS cross-tab that shows all zeros

Add the metering status breakdown

IBEDC serves prepaid, postpaid, and unmetered customers. Understanding the metering mix per region reveals where unmetered customers (the hardest to bill accurately) are concentrated.

  • Leave a blank row below the cross-tab (row 16), then type a section header in cell A17:
A17: Metering Status by Region
  • Set up the table headers in row 18:
A18: Region
B18: Prepaid
C18: Postpaid
D18: Unmetered
E18: Metering Rate %
F18: Avg Payment (Prepaid)
G18: Avg Payment (Postpaid)
H18: Avg Payment (Unmetered)
  • Type the region names in cells A19 through A23:
A19: Ibadan
A20: Oyo
A21: Osun
A22: Ogun
A23: Kwara
  • Enter these two-criteria COUNTIFS formulas in cells B19, C19, and D19 to count customers by meter type in the Ibadan region:
B19: =COUNTIFS(BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Prepaid")
C19: =COUNTIFS(BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Postpaid")
D19: =COUNTIFS(BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Unmetered")
  • Enter the Metering Rate formula in cell E19. This calculates the percentage of customers who have a meter (prepaid or postpaid):
=(B19+C19)/(B19+C19+D19)
  • Enter AVERAGEIFS formulas in cells F19, G19, and H19 to show average payment amount by meter type for Ibadan:
F19: =AVERAGEIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Prepaid")
G19: =AVERAGEIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Postpaid")
H19: =AVERAGEIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Unmetered")

What does the Metering Rate reveal?

The formula =(Prepaid+Postpaid)/(Prepaid+Postpaid+Unmetered) shows the proportion of customers with any kind of meter. A low metering rate indicates more estimated billing, which typically leads to higher revenue losses.

AVERAGEIFS with two criteria (Region + MeterType) lets you compare average payment behavior across meter types. Prepaid customers often pay more reliably because the meter cuts off supply when credit runs out.

  • Replicate the formulas in rows 20 through 23, updating the region name to "Oyo" in row 20.
  • Use "Osun" in row 21.
  • Use "Ogun" in row 22.
  • Use "Kwara" in row 23.
  • Format column E (E19:E23) as a percentage. Select the cells.
  • Click Home in the ribbon.
  • Open the Number Format dropdown and select Percentage.
  • Save your workbook with Ctrl+S.
  • Spot-check one value manually. Filter your BillingRecords sheet by one region and one meter type, then count the visible rows. The count should match the corresponding COUNTIFS cell on your Regional Analysis sheet.

Seeing #DIV/0! in the Metering Rate column?

  • This error means the denominator (total of Prepaid + Postpaid + Unmetered) equals zero for that region. Confirm that the COUNTIFS cells in columns B, C, and D show values greater than zero.
  • Check that your MeterType column uses the exact values Prepaid, Postpaid, and Unmetered with no extra spaces or different capitalization.

help me fix #DIV/0! errors in my metering rate calculations

✔️ Awesome, I've got everything!

Great. Double-check that you have saved your workbook and that all three sections (summary table, tariff cross-tab, metering status) show values without errors.

ⓧ I'd like to double check the full formulas

Here are all the formulas on your Regional Analysis sheet. The pattern repeats for each region. Replace the region name for each row.

=== REGION SUMMARY TABLE (Rows 1-6) ===
Headers: Region | Total Energy (kWh) | Total Billed (NGN) | Total Paid (NGN) | Collection Rate % | Record Count | Avg Bill (NGN)

Row 2 (Ibadan):
B2: =SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan")
C2: =SUMIFS(BillingRecords[AmountBilled_NGN],BillingRecords[Region],"Ibadan")
D2: =SUMIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan")
E2: =D2/C2
F2: =COUNTIFS(BillingRecords[Region],"Ibadan")
G2: =AVERAGEIFS(BillingRecords[AmountBilled_NGN],BillingRecords[Region],"Ibadan")

Rows 3-6: Same pattern with "Oyo", "Osun", "Ogun", "Kwara"

=== TARIFF BAND CROSS-TAB (Rows 9-14) ===
A9: Energy by Region and Tariff Band (kWh)
Headers (Row 9): blank | A | B | C | D | E
Regions in A10:A14 (Ibadan, Oyo, Osun, Ogun, Kwara)

B10: =SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan",BillingRecords[TariffBand],"A")
C10: =SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan",BillingRecords[TariffBand],"B")
D10: =SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan",BillingRecords[TariffBand],"C")
E10: =SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan",BillingRecords[TariffBand],"D")
F10: =SUMIFS(BillingRecords[EnergyBilled_kWh],BillingRecords[Region],"Ibadan",BillingRecords[TariffBand],"E")

Rows 11-14: Same pattern with "Oyo", "Osun", "Ogun", "Kwara"

=== METERING STATUS TABLE (Rows 17-23) ===
A17: Metering Status by Region
Headers (Row 18): Region | Prepaid | Postpaid | Unmetered | Metering Rate % | Avg Payment (Prepaid) | Avg Payment (Postpaid) | Avg Payment (Unmetered)
Regions in A19:A23 (Ibadan, Oyo, Osun, Ogun, Kwara)

B19: =COUNTIFS(BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Prepaid")
C19: =COUNTIFS(BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Postpaid")
D19: =COUNTIFS(BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Unmetered")
E19: =(B19+C19)/(B19+C19+D19)
F19: =AVERAGEIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Prepaid")
G19: =AVERAGEIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Postpaid")
H19: =AVERAGEIFS(BillingRecords[AmountPaid_NGN],BillingRecords[Region],"Ibadan",BillingRecords[MeterType],"Unmetered")

Rows 20-23: Same pattern with "Oyo", "Osun", "Ogun", "Kwara"

Your Regional Analysis sheet now reveals how energy, revenue, and metering coverage vary across IBEDC's five regions. Next up, you will build a Field Officer Scorecard that grades individual performance using IFS logic and text functions.

Build the Field Officer Scorecard

Your Regional Analysis sheet already reveals how IBEDC's five regions perform on energy distribution and revenue collection. Now you need to drill down to the individual level.

Management wants a scorecard that grades each field officer on their collection rate, summarizes their performance in plain English, and calculates their tenure. You will combine IFS logic, error handling, text functions, and date functions into one sheet.

In this step, get ready to:
  • Calculate each officer's total revenue collected and assign a performance grade using IFS.
  • Build summary text and date enrichment columns using TEXTJOIN, LEFT, NETWORKDAYS, and EOMONTH.
  • Apply conditional formatting with color fills, data bars, and icon sets to highlight performance visually.
Create the Scorecard sheet and calculate collection metrics

Each field officer is assigned to a business hub. You can measure their collection performance by totalling how much revenue their hub actually collected from the BillingRecords sheet, then comparing that total to their monthly target.

  • Right-click any sheet tab at the bottom of the workbook and select Insert > Worksheet.
  • Double-click the new tab and rename it Scorecard.
  • Switch to the FieldOfficers sheet from earlier.
  • Select all six columns of officer data (OfficerID through MonthlyCollectionTarget_NGN) and copy them with Ctrl+C.
  • Switch back to the Scorecard sheet, click cell A1, and paste with Ctrl+V.

You should now see all 10 field officers listed with their details in columns A through F.

  • Click any cell inside the pasted data and press Ctrl+T to convert it into an Excel Table.
  • Confirm the range covers all 10 rows plus headers, and check My table has headers.
  • Click OK.

Why convert to a Table?

Excel Tables give you structured references like [@BusinessHub] that auto-fill as you add formulas.

They also make your formulas self-documenting because you see column names instead of cryptic cell addresses like D2.

Now add the TotalCollected column that pulls each officer's revenue from the BillingRecords sheet.

  • Click the first empty column header cell (G1) and type TotalCollected as the header.
  • In cell G2, enter this formula:
=IFERROR(SUMIFS(BillingRecords[AmountPaid_NGN],BillingRecords[BusinessHub],[@BusinessHub]),0)

What does this formula do?

  • SUMIFS adds up all values in the AmountPaid_NGN column from BillingRecords where the BusinessHub matches the current officer's hub.
  • IFERROR wraps the whole thing so that if an officer's hub has no matching billing records, the cell shows 0 instead of an error.
  • Press Enter. The formula auto-fills down all 10 rows because the data is in a Table.

Next, add the percentage of target and the IFS-based performance grade.

  • In cell H1, type the header % of Target.
  • In cell H2, enter this formula:
=[@TotalCollected]/[@MonthlyCollectionTarget_NGN]
  • Press Enter to let it auto-fill.
  • In cell I1, type the header Grade.
  • In cell I2, enter this formula:
=IFS([@[% of Target]]>=1,"Excellent",[@[% of Target]]>=0.85,"Good",[@[% of Target]]>=0.7,"Fair",TRUE,"Needs Improvement")

How does IFS work here?

IFS evaluates conditions top to bottom and returns the value for the first condition that is TRUE.

  • If the officer collected 100% or more of target, they get "Excellent".
  • 85% or more gets "Good". 70% or more gets "Fair".
  • TRUE at the end acts as a catch-all for anything below 70%, returning "Needs Improvement".
  • Press Enter and confirm grades appear for all 10 officers.
  • Save your workbook with Ctrl+S.

You should see the Grade column populated with values like "Excellent", "Good", "Fair", or "Needs Improvement" depending on each officer's collection performance.

Seeing #VALUE! in the Grade column?

  • Check that your % of Target column header is exactly % of Target (with spaces). The IFS formula references it as [@[% of Target]] with square brackets around the name because it contains spaces.
  • Confirm the TotalCollected column has numeric values, not text. If IFERROR returned text "0" instead of numeric 0, the division in % of Target will fail.

Help me debug my IFS formula in the Scorecard sheet.

Add text and date enrichment columns

Management reports often need a human-readable summary sentence and tenure data for each officer. You will use TEXTJOIN to assemble a sentence from cell values, LEFT to extract first names, and date functions to calculate working days.

  • In cell J1, type the header SummaryText.
  • In cell J2, enter this formula:
=TEXTJOIN(" ",TRUE,[@OfficerID],"in",[@BusinessHub],"hub achieved",[@Grade],"rating with",TEXT([@TotalCollected],"#,##0"),"NGN collected")

What does TEXTJOIN do here?

TEXTJOIN joins multiple text values with a delimiter (here, a space). The TRUE argument tells it to skip any empty cells.

TEXT([@TotalCollected],"#,##0") formats the number with thousand separators so the sentence reads naturally, like "1,250,000 NGN collected".

  • Press Enter to auto-fill.

Now add the remaining enrichment columns for first name, tenure, and next review date.

  • In cell K1, type FirstName.
  • In cell K2, enter this formula:
=LEFT([@FullName],FIND(" ",[@FullName])-1)
  • In cell L1, type TenureDays.
  • In cell L2, enter this formula:
=NETWORKDAYS([@HireDate],TODAY())
  • In cell M1, type NextReviewDate.
  • In cell M2, enter this formula:
=EOMONTH(TODAY(),0)

What do these date functions calculate?

  • LEFT with FIND extracts everything before the first space in the officer's full name, giving you just the first name.
  • NETWORKDAYS counts only working days (Monday to Friday) between the hire date and today. Weekends are excluded automatically.
  • EOMONTH(TODAY(),0) returns the last day of the current month. This is the next performance review date.
  • Press Enter on each formula to auto-fill.
  • Save your workbook with Ctrl+S.

Your Scorecard should now show a summary sentence for each officer (column J), their first name (K), tenure in working days (L), and the next review date (M).

Seeing #VALUE! in the FirstName column?

  • Make sure FullName values contain a space between first and last name. FIND looks for a space character, and if there is none, it errors out.
  • If any officer has a middle name or double-barrelled surname, LEFT will still grab only the text before the first space.

Help me fix the #VALUE! error in my FirstName formula.

Apply conditional formatting to highlight performance

Conditional formatting turns raw data into visual signals. You will apply color fills to the Grade column, data bars to TotalCollected, and icon sets to % of Target.

Start with the Grade column color fills.

  • Select the Grade column cells (I2:I11).
  • Go to the Home tab.
  • In the Styles group, click Conditional Formatting > Highlight Cells Rules > Text that Contains.
  • Type Excellent and select Green Fill with Dark Green Text. Click OK.
  • Repeat for Good with Yellow Fill with Dark Yellow Text.
  • Repeat for Fair with Custom Format set to a light blue fill.
  • Repeat for Needs Improvement with a red fill using Custom Format.

Use Custom Format for non-default colors

For light blue and red fills, choose Custom Format from the format dropdown in the dialog. This opens the Format Cells dialog where you can pick any fill color from the Fill tab.

Next, add data bars to the TotalCollected column.

  • Select the TotalCollected column cells (G2:G11).
  • Click Conditional Formatting > Data Bars.
  • Choose any Gradient Fill style (blue is a good default).

Finally, add icon sets to the % of Target column.

  • Select the % of Target column cells (H2:H11).
  • Click Conditional Formatting > Icon Sets.
  • Select the 3 Arrows (Colored) option from the Directional section.
  • Save your workbook with Ctrl+S.

Your Scorecard now visually highlights performance. Green-filled "Excellent" grades stand out, data bars show relative collection amounts at a glance, and arrows indicate how each officer tracks against their target.

Data bars not appearing?

  • Confirm you selected only the data cells (G2:G11), not the header. Conditional formatting on headers can produce unexpected results.
  • If TotalCollected shows 0 for every officer, your SUMIFS may not be matching any BusinessHub values. Check that the hub names on Scorecard match the hub names on BillingRecords exactly (including spaces).

Help me troubleshoot my conditional formatting on the Scorecard.

✔️ Awesome, I've got everything!

Great. Double check you have saved your workbook.

ⓧ I'd like to double check the full code

Here are all the formulas on the Scorecard sheet. Each formula goes in the first data row of its column and auto-fills down.

Column G - TotalCollected:
=IFERROR(SUMIFS(BillingRecords[AmountPaid_NGN],BillingRecords[BusinessHub],[@BusinessHub]),0)

Column H - % of Target:
=[@TotalCollected]/[@MonthlyCollectionTarget_NGN]

Column I - Grade:
=IFS([@[% of Target]]>=1,"Excellent",[@[% of Target]]>=0.85,"Good",[@[% of Target]]>=0.7,"Fair",TRUE,"Needs Improvement")

Column J - SummaryText:
=TEXTJOIN(" ",TRUE,[@OfficerID],"in",[@BusinessHub],"hub achieved",[@Grade],"rating with",TEXT([@TotalCollected],"#,##0"),"NGN collected")

Column K - FirstName:
=LEFT([@FullName],FIND(" ",[@FullName])-1)

Column L - TenureDays:
=NETWORKDAYS([@HireDate],TODAY())

Column M - NextReviewDate:
=EOMONTH(TODAY(),0)

Conditional formatting applied:

  • Grade column (I2:I11): color fills. Excellent = green, Good = yellow, Fair = light blue, Needs Improvement = red.
  • TotalCollected column (G2:G11): data bars (gradient fill).
  • % of Target column (H2:H11): icon sets (3 arrows).

Secret mission

Build a PivotTable Dashboard with Slicers

Your analysis workbook is powerful, but it requires scrolling between sheets and manually reading numbers. Turn your IBEDC data into an interactive dashboard where managers can filter by region and tariff band with a single click.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally using Excel 2019 and local CSV files. There are no cloud services, subscriptions, or ongoing costs to worry about.

Resources you used:

  • IBEDC_Distribution_Analysis.xlsx workbook (seven sheets, Power Query connections, PivotTables, PivotCharts, and slicers).
  • billing_records.csv source data file.
  • feeders.csv source data file.

Keep everything running

No action needed. Choose this if you want to keep your workbook as a portfolio piece to demonstrate your Excel data analysis skills to employers.

  • Your workbook and CSV files stay where you saved them on your local machine.
  • The Power Query connections inside the workbook will continue to work as long as the CSV files remain in the same location.
  • Consider uploading to OneDrive or emailing to yourself as a backup.

Pause - I'll come back to this later

Close the workbook and free up your working space. Your files remain on disk so you can pick up where you left off.

  • Save and close IBEDC_Distribution_Analysis.xlsx in Excel.
  • Note the folder location where your workbook and CSV files are saved so you can find them later.
  • When you reopen the workbook, Power Query connections will refresh automatically if the CSV files are still in the same location.

Delete - I don't want to use this again

Remove all project files from your machine. This cannot be undone once you empty the Recycle Bin.

  • Close IBEDC_Distribution_Analysis.xlsx if it is still open in Excel.
  • Open File Explorer and navigate to the folder where you saved your project files.
  • Select IBEDC_Distribution_Analysis.xlsx, billing_records.csv, and feeders.csv.
  • Press the Delete key, then empty your Recycle Bin to permanently remove them.

Nice Work!

Nice Work!

You just built a complete energy distribution analysis workbook for IBEDC, demonstrating the exact data analysis skills employers test in hiring assessments.

Here's what you accomplished:

  • Imported and cleaned messy CSV data using Power Query (Get & Transform), including removing duplicates, trimming whitespace, splitting combined columns, and changing data types.
  • Built cross-sheet lookups with VLOOKUP and INDEX/MATCH to connect billing records to feeder details, and handled errors gracefully with IFERROR.
  • Created multi-criteria aggregations using SUMIFS, COUNTIFS, and AVERAGEIFS to analyze energy distribution, revenue collection, and metering status across IBEDC's five regions. Then built a Field Officer Scorecard with IFS performance grading, TEXTJOIN report summaries, date functions, and conditional formatting.
  • Secret Mission: Created an interactive PivotTable dashboard with PivotCharts and slicers for filtering IBEDC energy data by region and tariff band.

Ready to quiz yourself?