Source: Business Analytics Foundations, 5th Ed., Eric C. Larson, Ch. 1
Tags: Excel, spreadsheet, cell reference, absolute reference, relative reference, array, freeze panes, sorting, filtering, data formatting, BADM 210, business analytics
Difficulty: Beginner Prerequisites: None. This is the foundation for everything else in the course.
Excel is the default tool for data work across virtually every business function, from finance to marketing to operations. This chapter covers the ground-level mechanics: how spreadsheets are structured, how you reference and navigate data, and how you organise it through formatting, sorting, and filtering. If you are comfortable with these basics, every later topic in Business Analytics Foundations will land more easily. If you are not, later chapters on statistical analysis and decision-making will feel unnecessarily hard.
Excel organises data into cells (identified by column letter and row number) on sheets within a workbook. You reference cells with the "=" sign, lock references with "$" for absolute referencing, and select blocks of cells as arrays using the ":" notation. Sorting reorders all your data by a chosen variable; filtering hides rows that do not meet your criteria.
Cell
The smallest unit in an Excel sheet. A single box that holds one piece of data. Each cell has a unique identifier made up of its column letter and row number (e.g. A1, B7, Z101).
In simple terms, think of it as one box in a very large grid.
Cell identifier (cell address)
The column letter plus the row number that uniquely names a cell. Column A, Row 3 gives the identifier A3.
Sheet (worksheet)
A single page within an Excel workbook. Each sheet is its own grid of cells where you create, manipulate, and view data.
Workbook
The entire Excel file. A workbook can contain multiple sheets.
Cell reference
A formula that points to the contents of another cell. Created by typing "=" followed by the target cell's identifier (e.g. =A1). Excel then displays whatever value that target cell holds.
In simple terms, it is a way of saying "show me what is in that other box."
Relative reference
The default referencing behaviour. When you copy a formula to a new location, the reference shifts by the same number of rows and columns you moved. If =A1 is in cell B2 and you drag it down to B3, it becomes =A2 automatically.
Absolute reference
A reference locked with the "$" symbol so that it does not shift when copied. "$" before the column letter locks the column; "$" before the row number locks the row; both together (e.g. $A$1) lock the reference completely.
In simple terms, it is a way of telling Excel "always look at this exact cell, no matter where I copy the formula."
Mixed reference
A reference where only the column or only the row is locked (e.g. $A1 locks the column but not the row; A$1 locks the row but not the column).
Array (range)
A rectangular block of connected cells, referenced by the top-left cell and bottom-right cell separated by a colon (e.g. A1:C4 selects every cell from columns A through C and rows 1 through 4).
Header row
The first row in a dataset, typically containing labels that describe what each column holds (e.g. "Name", "Salary", "Department").
Freeze panes
A View-tab feature that keeps selected rows or columns visible on screen while you scroll through the rest of the data.
Sorting
Reordering all rows in a dataset based on the values in one or more columns. Ascending (A to Z, smallest to largest) or descending (Z to A, largest to smallest). All data stays in the table; only the order changes.
Filtering
Temporarily hiding rows that do not meet specified criteria. Unlike sorting, filtering excludes data from view entirely until the filter is removed.
Custom Sort
An advanced sorting option that lets you add multiple layers of sorting (e.g. sort first by department, then by salary within each department).
Every Excel file is a workbook. Each workbook contains one or more sheets.
Each sheet is a grid. Columns run vertically and are labelled with letters (A, B, C, ...). Rows run horizontally and are labelled with numbers (1, 2, 3, ...).
A cell sits at the intersection of one column and one row. Its identifier combines the column letter and row number: cell B3 is column B, row 3.
A cell holds one piece of data: a number, a piece of text, a date, or a formula.
To reference another cell, type "=" followed by that cell's identifier. For example, typing =A1 in cell B2 tells Excel to display A1's value in B2.
You can also click the target cell instead of typing its identifier.
The "=" symbol signals to Excel that what follows is a formula or reference, not raw data entry.
To copy a formula, select the cell containing it, hover over the small green square at the bottom-right corner until a "+" cursor appears, then drag in any direction.
By default, references are relative: they adjust based on how far you drag. Dragging =A1 one row down turns it into =A2. Dragging it one column right turns it into =B1.
This behaviour is useful when applying the same calculation across many rows or columns of data.
Place a "$" before the column letter to lock the column: =$A1 always points to column A, but the row adjusts when copied.
Place a "$" before the row number to lock the row: =A$1 always points to row 1, but the column adjusts when copied.
Place "$" before both to lock the entire reference: =$A$1 never changes regardless of where you copy the formula.
Pressing F4 (Windows) or Cmd+T (Mac) while editing a cell reference cycles through the four combinations: A1, $A$1, A$1, $A1.
An array is a rectangular selection of connected cells, referenced with a colon between the top-left and bottom-right cells.
Example: =A1:C4 selects every cell in columns A through C, rows 1 through 4 (twelve cells total).
Arrays are used as inputs for most Excel functions (SUM, AVERAGE, COUNT, and so on).
Found under the View tab. Three options:
Freeze Panes: freezes all rows above and all columns to the left of the currently selected cell. Selecting the correct cell before clicking is essential.
Freeze Top Row: freezes only row 1, regardless of which cell is selected. Useful when your header row is at the top.
Freeze First Column: freezes only column A, regardless of which cell is selected.
Right-click a sheet tab, select "Move or Copy."
You can move or copy the sheet to a different location within the same workbook or to another open workbook.
Tick the "Create a copy" checkbox if you want to duplicate the sheet. Without the tick, the sheet is moved, not copied.
On Mac, right-click is done by holding Control and clicking.
File > Save saves the current file. File > Save As lets you rename it, choose a different location, or change the file format.
The file format dropdown in Save As lets you switch between .xlsx, .csv, .xlsm, and other formats.
Excel appends the format extension automatically, so you do not need to type it in the file name.
The Home ribbon's Number section provides quick formatting buttons:
"$" button formats as US currency. The adjacent dropdown offers other currencies.
"%" button formats as a percentage.
"," button adds comma separators (thousands, millions).
The increase/decrease decimal buttons adjust decimal places shown.
The dropdown at the top of the section (defaulting to "General") gives access to common formats. Selecting "Custom" at the bottom opens the full list.
Select the data range, then click Sort & Filter on the Home ribbon (or use the Data ribbon, which works identically).
Sort A to Z (ascending) or Z to A (descending) for a quick single-column sort.
Custom Sort opens a dialog for multi-level sorts. You can specify which column to sort on, whether to sort by values, cell colour, font colour, or cell icon, and the sort order. Example: sort student records by class year first, then by GPA within each year.
Select the headers of your data, then choose Filter from the Sort & Filter menu. Small dropdown arrows appear on each header cell.
Click a column's dropdown to open the filter dialog. Options include:
Filter by colour: cell colour, font colour, or cell icon.
Condition-based filters: for categorical data (Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain); for numerical data (Equals, Greater Than, Less Than, Between, Top 10, Bottom 10, Above Average, Below Average).
Checkbox selection: scroll through unique values in the column and tick or untick them individually. A search bar helps with large lists.
Combine multiple conditions with "And" (both must be true) or "Or" (either can be true).
The filter dialog also includes sorting options, so you can sort and filter in one step.
Syntax | What It Does |
|---|---|
=A1 | References cell A1 (relative) |
=$A$1 | Absolute reference to A1 |
=$A1 | Column locked, row relative |
=A$1 | Row locked, column relative |
=A1:C4 | Selects the array from A1 to C4 |
Sorting and filtering are how analysts pull actionable subsets from large datasets. A sales manager might filter a 50,000-row order log to show only orders from a specific region, then sort by revenue to identify the top accounts. An HR analyst might sort employee data by tenure and filter for a single department to plan succession.
Students often assume that copying a formula keeps the same cell references. It does not. By default, references are relative and shift with the direction you copy. Use "$" if you need to lock a reference.
Students sometimes confuse sorting with filtering. Sorting keeps all data visible but rearranges the order. Filtering hides rows that do not meet the criteria. The data is still there, just not displayed.
Freezing panes does not lock cell contents or prevent editing. It only keeps certain rows or columns visible on screen while you scroll.
Students forget to tick "Create a copy" when duplicating a sheet, and accidentally move it instead. The original location then has no copy of that sheet.
⚠️ Know the difference between relative, absolute, and mixed references, and be able to predict what happens when a formula is copied to a new cell.
⚠️ Understand the distinction between sorting (reorder) and filtering (hide/exclude). Exam questions may describe a scenario and ask which operation the analyst should use.
⚠️ Be able to identify the correct Freeze Panes option for a given layout (e.g. freezing the top row versus freezing both a row and a column).
⚠️ Know that "Custom Sort" allows multi-level sorting and can sort by values, colour, or icons.
True or False: If you type =B3 in cell D5 and copy the formula to D6, it becomes =B4. True
True or False: The "$" symbol in =$A1 locks both the column and the row. False (it locks only the column)
Fill in the blank: To select every cell from A1 to D10, you type =A1____D10. : (colon)
True or False: Filtering permanently deletes rows that do not match your criteria. False (they are hidden, not deleted)
True or False: Freeze Top Row freezes whichever row your cursor is in. False (it always freezes Row 1)
Q: An analyst has =A1 in cell B2. They copy this formula to cell C3. What cell does the formula now reference?
A: B2. The formula shifted one column to the right and one row down, mirroring the move from B2 to C3.
Q: An analyst needs a formula in B2 that always points to cell A1, no matter where the formula is copied. What should they type?
A: =$A$1
Q: What is the difference between sorting and filtering data in Excel?
A: Sorting rearranges all rows in the dataset based on the values in a chosen column. All data remains visible. Filtering hides rows that do not meet specified criteria, temporarily removing them from view.
Q: An analyst wants to keep the header row visible while scrolling through 10,000 rows of data. Which Freeze Panes option should they use?
A: Freeze Top Row (found under the View tab).
Q: Describe a scenario where Custom Sort is more appropriate than a basic A-to-Z sort.
A: When the analyst needs to sort by more than one criterion, such as sorting employees first by department (alphabetically) and then by salary (highest to lowest) within each department.
This material is the foundation for every subsequent chapter in Business Analytics Foundations. Functions (covered in Part 2 of these notes) build directly on cell referencing and arrays. Later topics on descriptive statistics, probability, and regression all assume fluency with sorting, filtering, and data formatting, because preparing and exploring data is always the first step before any analysis.
Excel basics, spreadsheet fundamentals, cell address, cell identifier, relative reference, absolute reference, mixed reference, dollar sign Excel, freeze panes, freeze top row, freeze first column, sort A to Z, custom sort, filter data, Excel filter dialog, data formatting, number format, currency format, percentage format, BADM 210, BADM210, business analytics foundations, University of Illinois, Gies College of Business, Eric Larson, introductory Excel, beginner Excel