Excel Basics: Charts, Functions, and Data Management

Excel Basics Overview

• Excel is a powerful tool designed to transform raw data into meaningful insights through features such as charts, functions, and cell referencing.

• The foundational skills covered include mastering basic data analysis tasks, creating and customizing charts, utilizing functions, and understanding the distinction between absolute and relative cell referencing.

• Practice exercises utilize the file <Intro.xlsx> and its corresponding solution file <Intro solutions.xlsx>.

• The dataset in the worksheet test consists of names for $20$ students in column $A$, divided into three groups ($A$, $B$, and $C$) in column $C$. Test scores are located in column $B$.

• Key performance thresholds are defined in cells $J2$ (passing score, e.g., $10$ out of $20$) and $J3$ (tolerable score).

Charts and Data Visualization

• Excel allows for various graph types depending on the nature of the data. For introductory purposes, the focus is on general functionality and common chart types like box plots.

Creating a Box Plot (Box & Whisker)

1. Navigate to the Chart worksheet.

2. Select the non-empty cells in column $B$.

3. Go to the Insert Tab and select the Box & Whisker option.

4. The chart appears in the Excel grid and can be moved or resized as needed.

Customizing Chart Information

• Removing the Title: Select the chart, go to the Chart Design contextual tab, click Add Chart Element, then Chart Title, and select none.

• Adding Axis Titles: Select Add Chart Element > Axis Titles > Primary Vertical. Click the resulting text box and enter a descriptive title, such as test scores.

• Removing Non-informative Labels: Individual labels, such as the 1 on the $x$-axis of a single-series box plot, can be removed by selecting the label and pressing the delete button.

Chart-Data Interaction

• Live Updates: If a value in the highlighted data range is adjusted (e.g., increasing the largest score by $1$), the chart reflects the change instantly (e.g., the box plot extends over a larger range on the $y$-axis).

• Select Data Command: The Select Data command under the Chart Design tab displays the specific data range used for the graph.

Chart Formatting Areas

Right-clicking different parts of a chart opens specific formatting panes. These areas include:

1. Chart Title: Accessible via Format Chart Title.

2. Axis Title: Accessible via Format Axis Title.

3. Axis: Right-clicking the numbers on an axis brings up Format Axis.

4. Chart Area: The area outside the actual graph figure; brings up Format Chart Area.

5. Plot Area: The rectangle where the graph is displayed but not on the data figure itself; brings up Format Plot Area.

6. Data Series: Clicking directly on the data figure brings up Format Data Series.

Axis Bounds Adjustment

To set a custom scale for the $y$-axis:

1. Right-click the vertical scale and select Format Axis.

2. In the Axis Options pane, locate the Bounds section.

3. Enter a value (e.g., $4$) in the Minimum field and press Enter.

Excel Functions and Syntax

Entry Methods

1. Direct Typing: Typing the formula starting with an equas sign ($=$) directly into a cell.

2. fx Button: Clicking the $fx$ button next to the formula bar permits building commands through a dialog screen.

3. Formula Tab: Selecting the Formula Tab in the ribbon to browse function categories.

The SUM Function

• Manual Calculation Example: To find the average for group $A$ (students at rows $2, 3, 4, 20, 21$) in cell $H27$: $=(B2+B3+B4+B20+B21)/5$.

• Range Summation: To sum all test scores and divide by $20$ in cell $C27$: $=SUM(B2:B21)/20$.

• Syntax: $SUM(number1; [number2]; ...)$.

  • Arguments in square brackets (e.g., $[number2]$) are optional.

  • The first argument must be a number, cell, or range.

  • If the list separator on your system is a comma, replace the semicolons in these examples with commas.

• Multi-range SUM: You can sum non-contiguous ranges, such as $=SUM(B2:B4; B20:B21)/5$ in cell $J27$.

The COUNT and COUNTA Functions

• $=COUNT(range)$: Counts the number of cells in a specified range that contain numerical values. Example: $=COUNT(B2:B21)$ identifies the sample size of scores.

• $=COUNTA(range)$: Counts the number of cells in a range that are not empty, regardless of whether they contain numbers or text. Example: $=COUNTA(A2:A21)$ counts all name entries.

Functions with Criteria

The COUNTIF and COUNTIFS Functions

• $=COUNTIF(range; criteria)$: Counts cells meeting a specific condition.

  • Numbers: Quotation marks are not needed. Example for counting zeroes: $=COUNTIF(B2:B21; 0)$.

  • Text: Must be enclosed in double quotes. Example for Group A: $=COUNTIF(C2:C21; "A")$.

  • Logical Expressions: Must be enclosed in double quotes. Example for "Not Group A": $=COUNTIF(C2:C21; "<>A")$. Example for passing scores ($≥ 10$): $=COUNTIF(B2:B21; ">=10")$.

  • Combined with Cell References: Use the ampersand ($\&$) to join the logical operator and the cell. Example using cell $J3$ for tolerance: $=COUNTIF(B2:B21; ">="\&J3)$.

• $=COUNTIFS(criteria\_range1; criteria1; criteria\_range2; criteria2; ...)$: Counts cells that meet multiple criteria simultaneously. Example: $=COUNTIFS(B2:B21; ">=10"; C2:C21; "A")$ counts group $A$ students who passed.

The SUMIF Function

• $=SUMIF(range; criteria; [sum\_range])$: Adds cells based on a condition.

• Example: $=SUMIF(C2:C21; "A"; B2:B21)/H24$ calculates the average score for group $A$ by dividing the sum of their scores by the count of students in group $A$.

The IF Function

• Checks whether a condition is true or false and returns defined values for each outcome.

• Syntax: $IF(logical\_test; value\_if\_true; value\_if\_false)$.

• Example: In cell $D2$, $=IF(B2>=10; "pass"; "no pass")$.

• Unlike $COUNTIF$, the condition is a single argument, and quotes are only used for text values, not for the logical expression itself.

Cell Referencing: Relative vs. Absolute

Relative Cell Referencing

• By default, Excel uses relative referencing. If you copy a formula from cell $D2$ to $D3$, the reference $B2$ automatically adjusts to $B3$ to reflect the move one row down.

• If a formula is copied across columns (e.g., from $D2$ to $E3$), both the row and the column references will adjust accordingly.

Absolute Cell Referencing

• To prevent a reference from changing when copied, dollar signs (\) are used to "fix" the cell.

• Fixing the Row: $J\$3$ fixes the row number. If copied horizontally, the column changes; if copied vertically, the row stays fixed.

• Fixing the Column: $\$J3$ fixes the column letter. If copied vertically, the row changes; if copied horizontally, the column stays fixed.

• Fixing Both (Absolute): $\$J\$3$ fixes both the row and column. The reference will always point to cell $J3$ regardless of where the formula is pasted.

• Practice Example: To check scores against a fixed tolerance level in $J3$ while dragging a formula down: $=IF(B2>=\$J\$3; "tolerable"; "not tolerable")$.

Data Editing and Management

Sorting Data via Ribbon

1. Select the data range (e.g., $A1:C21$).

2. Navigate to Home > Sort & Filter.

3. Sort A to Z: Sorts alphabetically based on the first column selected.

4. Custom Sort: Allows sorting by specific variables and multiple levels.

   • Headers: Ensure "My list has headers" is checked if the selection includes titles.

   • Multiple Levels: Use the $+$ sign to add sorting layers (e.g., first by group, then by name).

   • Order: Options include "A to Z", "Z to A", or "Largest to Smallest".

Filtering Data via Ribbon

1. Select headers (e.g., $E56:G56$).

2. Go to Home > Sort & Filter > Filter.

3. Dropdown arrows appear; click them to select specific criteria (e.g., only checking Group $B$).

4. Equations: Filters can specify conditions like "Equals", "Greater Than", etc.

Dynamic Array Functions

Unlike Ribbon sorting/filtering, these functions update automatically when source data changes. (Requires Excel 365 or 2019).

• SORT:

  • Syntax: $=SORT(array; [sort\_index]; [sort\_order]; [by\_col])$.

  • Example: $=SORT(A2:C21; 2; -1)$ sorts by the second column in descending order.

• SORTBY:

  • Syntax: $=SORTBY(array; by\_array1; [sort\_order1]; [by\_array2]; [sort\_order2]; ...)$.

  • Example: $=SORTBY(A2:C21; C2:C21; 1; A2:A21; 1)$ sorts by group (ascending), then by name (ascending).

• FILTER:

  • Syntax: $=FILTER(array; include; [if\_empty])$.

  • Example: $=FILTER(A2:C21; C2:C21="B")$ extracts only the rows where the group is "B".

Managing Dynamic Results

• When copying results of dynamic arrays, relative cell referencing may cause errors.

• Paste Special: To copy the results without the underlying dynamic formula, use Ctrl+C, then right-click and select Paste Special > Values. This removes the dynamic link.

Inserting and Deleting Cells vs. Clearing Content

• Clearing: Selecting a cell and pressing Delete or using Clear All removes the content (and potentially formatting) but the cell remains in place.

• Deleting: Using the Delete command in the Home tab removes the cell itself.

  • Shift cells left/up: When a cell is deleted, surrounding cells must shift to fill the void.

• Deleting Rows/Columns: Selecting a row number or column letter and using the Delete command removes the entire structure.

• Inserting: The Insert command adds new rows or columns, or shifts existing cells (e.g., Shift cells right) to create space.

Summary of Essential Functions