Pre-Read Guide: Data to Dashboard in
Excel
Introduction to Excel Features
This guide is designed to help you build a strong foundation in Excel before the hands-on
dashboard creation session. Please go through each section step by step and practice using
the attached Excel file.
1. Basic Excel Features
• Excel Ribbon: Contains tabs like Home, Insert, Page Layout, Formulas, Data, Review, View.
• Worksheets: Each workbook contains one or more sheets where data is stored.
• Cells: Each cell is identified by its column letter and row number (e.g., A1).
2. Basic Formulas
Try these formulas in the attached Excel file (Sheet1):
• =SUM(A2:A10) → Adds numbers in a range.
• =AVERAGE(A2:A10) → Finds the average value.
• =MAX(A2:A10), =MIN(A2:A10) → Finds the largest and smallest values.
• =IF(B2>50, "Pass", "Fail") → Checks a condition and returns a result.
3. Advanced Formulas
• =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) → Searches for a value in a
table.
• =HLOOKUP(lookup_value, table_array, row_index_num, FALSE) → Similar to VLOOKUP but
searches horizontally.
• =INDEX(range, row_num, col_num) → Returns the value of a cell within a range.
• =MATCH(lookup_value, lookup_array, 0) → Returns the position of a value in a range.
• Combine INDEX + MATCH for flexible lookups.
• =LEN(A2), =TRIM(A2), =CONCATENATE(A2, B2) → Text functions for cleaning and
combining data.
4. Preparing Data for Dashboards
• Always keep your data in a tabular format: one row = one record, one column = one field.
• Avoid merged cells and blank rows.
• Use clear column headers.
5. PivotTables
PivotTables allow you to quickly summarize large datasets. Practice:
1. Select your data.
2. Go to Insert > PivotTable.
3. Drag fields into Rows, Columns, Values, and Filters.
6. PivotCharts & Slicers
• PivotCharts: Visualize your PivotTable data with charts.
• Slicers: Insert > Slicer to add interactive filters.
