Excel Workshop

Intermediate Concepts and Tips/Tricks

Jorge Flores, Jr.

Conditional Formatting

1.       Highlight your desired data/values, then at the top ribbon under Styles, select Conditional Formatting

2.        In the dropdown that appears, select Highlight Cell Rules and then Greater Than…

3.        Enter the desired value and change the dropdown to Green Fill with Dark Green Text

Pivot Tables

1.        Select a cell within your desired data array, then at the top ribbon tabs select Insert, then select PivotTable.

2.        Confirm that the Table/Range has selected your desired table, then select the Existing Worksheet radio button

3.        Click in the Location box, then select any blank area of your sheet to select where to insert the table, then press OK

4.        The PivotTable Fields allows you to manipulate the PT in a way that visualizes your data. Drag a column name from the box into one of the smaller boxes below:

a.        Filters – Turns the desired array into a dropdown filter to only show data based off the selected value. Example: Filter by Rep to only show what the selected rep sold

b.       Columns – Show the selected array in a list of columns

c.        Rows – Show the selected array in a list of rows

d.       Values – Perform a logical function for the selected array. Example: Sum of Total will add together all the total amounts of each sale

Advanced Formulas

1.        Select the cell where you would like to insert the formula, then at the top ribbon tabs select Formulas, then select the desired formula under each category. Example: Logical > IF

2.        In the Function Arguments windows, you can insert the formula values as needed. Examples:

a.        IF & IFS – The Logical_test will be the actual expression to test if the referenced cell is TRUE or FALSE. Value_if_true and value_if_false will return depending on what the result is. For IFS, you can have multiple tests and if one is true, it will return the respective result

b.       AND/OR – The Logical1/Logical2 will be the tests to return TRUE to ensure a final TRUE value is present. In OR, only one needs to be TRUE

c.        XLOOKUP – This is one of the more difficult but most helpful functions. The Lookup_value is the cell that holds the value you are trying to find. Example: Cell J3 can be the cell where you enter a specific date. Lookup_array is the area where the function looks for the specified value, in this case the whole table. Return_array is what you want to return when it finds the matching value. Example: just the item, units, unit cost, and total.

Data Validation

1.        Select the cell(s) you would like to apply the Data Validation, then at the top ribbon tabs select Data, then above Data Tools select the Data Validation button (green check and red cancel sign)

2.        In the Data Validation window under Settings, change the Allow: dropdown to your desired condition

a.        Date – The data must be a date between the Start date and the End date which can either be manually entered or selected from a table

b.       List – The Source dropdown lets you either manually enter or select the list of values to pick. Example: Select the Rep column to only select the listed Reps from the table

3.        Once completed, select OK

 

Filters

1.        At the top of a formatted table, select the down arrow of the desired column to reveal the Filters for that column

2.        Check or uncheck the data in the list that you would like to see

3.        Select Clear Filter from “xxx” to remove the filter

Absolute Reference

1.        Select a cell and enter the desired cell reference. Example: H3

2.        While still typing in the cell, press F4 on your keyboard to convert the reference to an absolute reference

3.        If you do not have function keys on your keyboard, simply add a $ before the letter and number of the reference.
Example: $H$3

AutoFill

1.        Create an initial pattern of cells such as numbers, dates, or names. Example: 5, 10, 15 or Jan, Feb, Mar

2.        Highlight the initial pattern, then select the bottom right corner of the selection once your mouse turns into a cross. Then drag in the direction you would like to continue the pattern

Labeling

1.        Select the desired cell/table/object, then at the top left corner of the sheet, enter the desired name to reference the cell/table/object

2.        Select the same dropdown list to then jump to the desired object across the workbook. This works throughout all your sheets

3.        Use the name in formulas and references for easy access to commonly used data points