Learn Excel Essentials

Summary

1. Find Functionality - Find cells with searched for data within workbook, worksheet, or highlighted sub-sections of worksheet

2. Replace Functionality - Find and replace cell data with entered replacement within workbook, worksheet, or highlighted sub-sections of worksheet

3. Go To Functionality - Find and select cells based on chosen criteria, e.g. blanks, last cell

4. Access Shortcuts via Home tab on ribbon to select constants, formulas, notes, conditional formatting, constants and data valdiation enabled cells

Summary

1. General Formatting tools and options

2. Date formats

3. Number Formats

4. Custom formats

5. Format Painter

6. Conditional formatting

Summary

1. Filtering via Data tab on ribbon - ensure you have headers above data

2. Filtering options via manual selection, typed selections and clearing filters

3. Filtering options for text data including searching for text starting with, ending with, containing chosen text and more options

4. Filtering options for numeric data including searching for numeric data using comparisons (greater than, less than etc)

5. Filtering options for date data including searching for dates in a range, before/after a set date and more options

6. Sorting data via options from arrow pull-down menu on headers or via Data tab on ribbon for more options

7. Sorting based on data values, or formats, or font colors, or conditional formatting

8. Sorting by multiple levels for increased control

Summary

1. Standard copy and paste with ctl+c to copy and ctrl+v to paste, or use right click menu option - will bring formats, values. comments, and move any cell references if relative references

2. Paste special via right click->'Paste Special...'

3. Certain oft-used and useful paste special options, e.g. formulas are available on right clicking, listed above the 'Paste Special...' heading. This shows previews of the cells you are highlighting to paste into to give an indicator of expected result

4. Fill handle left-click and dragging to copy down and generate any patterns excel spots, e.g. 1 and 2 in 2 adjacent cells, if using the fill handle will generate 3,4, 5 etc into adjacent cells dragged into via the fill handle

5. Ctrl+d to copy down a cell's contents or formula into all cells highlighted below it

6. Ctrl+r to copy right a cell's contents or formula into all cells highlighted to the right of it

7. Dealing effectively with copying and pasting in situations with filters applied

Summary

1. Password protecting workbooks for open or modify permissions

2. Protect workbook

3. Protect sheet

4. Allow edit Ranges

Attached is the end result of 'Build a Budget - Checkpoint 1' - you can use your own saved file, or this one, as the starting point for this video as we build on the spreadsheet.

Summary

1. Using IF to check a condition and return different responses based on whether the check is true or false

2. Using SUMIF to work through a range and check each cell in that range matches a condition, then adding cell value (if numeric) to a running total

3. Using COUNTIF to work through a range and check each cell in that range matches a condition, then adding to a running total of number of cells meeting the condition

Summary

1. Use the ALT key to highlight the keyboard key shortcut to access menus and tools in Excel, e.g. Ribbon Access via keyboard

2. Keyboard Shortcuts are a great tool to master, and will speed up your usage of excel, and bring efficiency

3. Takes time to embed these to muscle memory - keep practising!

4. List of many common shortcuts attached below - it is worth learning a few rather than all. For example ctrl+c, ctrl+v (copy/paste), ctrl+z,ctrl+y (undo/re-do) are great ones to remember.

Summary:

Excel shortcuts that I would recommend embedding to your regular usage of Excel to save time and be far more efficient:

Below are the Windows Shortcuts - there are downloadable PDFs here for these as a reference, for both Windows and Mac in an infographic format.

1. F2: Edit cell contents

2. Ctrl+`: Show Formulas

3. Keyboard arrow keys (Move), combined with shift (Move and highlight), and ctrl (Move, highlight, and jump to next blank cell)

4. Ctrl+z/Ctrl+y: Undo/Re-do

5. Ctrl+c/Ctrl+v: Copy/paste

6. Ctrl+[: Jumping to and selecting cells referenced in formulas, and also opening unopen files

7. Ctrl+Shift+L to apply filter and Alt+Down Arrow to open the filter, and Arrow keys + Space Bar to select items

8. Ctrl+Space Bar/Shift+Space Bar: Select entire columns / rows

9. Ctrl+Shift+Plus to add row/column, Ctrl+Minus to remove row/column

10. F4: Toggle Absolute/Relative Cell References with cell reference highlighted in edit cell mode

Summary

1. Range Names can be used to name a cell, or multiple cells (e.g. a table or matrix of cells) to allow logical identification within formulas as opposed to just cell references.

2. Range Names also allow jumping to those cells easily, making for more efficient spreadsheet navigation

3. Data Validation can be used to control data input, e.g. for limiting selections, or ensuring data entered is within a logically defined range

4. Data Validation can be combined with Range Names in limiting parameters

Summary

1. Text to columns functionality - using delimited option based on the character separting your data, e.g. commas, semi-colons etc

2. Csv Files - When working with a csv file, and having made changes such as the addition of formulas or formatting, be sure to save as .xlsx when done, rather than the .csv it originally was, as otherwise formulas and formatting will be lost

3. Remove Duplicates - to find unique values, and to strip entire rows of data based on duplicates found

Summary

1. Generation of charts based on data as input

2. Different types of chart possiblity

3. Combo-charts to combine e.g. bar charts and line charts for effective portrayal of data

4. Shifting charts to new sheets for improved presentation

Attached is the end result of 'Build a Budget - Checkpoint 2' - you can use your own saved file, or this one, as the starting point for this video as we build on the spreadsheet.

Summary

1. Use =VLOOKUP to find data in the first column of a highlighted array, Excel will then run down the rows in the first column to match a given value, and will return matched data in a specified column on the same row as the data found

2. .Use =HLOOKUP to find data in the first row of a highlighted array, Excel will then run across the columns in the first row to match a given value, and will return matched data in a specified row on the same column as the data found

3. VLOOKUP & HLOOLKUP only find the FIRST matching item in a given column/row if there are multiple items matching the value

4. VLOOKUP & HLOOKUP are useful to match data between data sets, e.g. for reconciliation purposes to see items are in all or one set of data

Summary

1. MATCH can be used to find the position of an item in a single row or column (or portions of a row or column)

2. INDEX can be used to return the value in a cell within a specified table array, referenced by a row and column

3. INDEX will return a reference of the cell rather than its value if passed into a function that works as such, e.g. SUM

4. INDEX can be used to return portions of, or the entirety of, the table as an array - older Excel versions may need to highlight the required area for the data to populate into and use ctrl+shift+enter to achieve this

5. INDEX can be combined with MATCH to achieve results similar to VLOOKUP/HLOOKUP, except with the added ability to find data and return a value to the left or above the found data, whereas VLOOKUP/HLOOKUP will return values found to the right or below only, respectively

Summary

1. Pivot Tables Usage

2. Pivot Charts Usage

3. Slicer Usage

4. Timeline Usage

Attached below is the end result of 'Build a Budget - Checkpoint 3' - you can use your own saved file, or this one, as the starting point for this video as we build on the spreadsheet.

Also below is the end result of 'Build a Budget - Checkpoint 4' - this is the final form of the budget spreadsheet you have built, to use as a reference to your own if needed.

Summary

1. Use Lookups on all sets of data to compare missing items in either and flag these via colours/comments etc to deal with as required

2. Create Unique combinations ('URNs') by concatenating (combining via using =A2&B2 for example to combine values in cells A2 and B2 into one item) cell values

3. Use Lookups on the URNs to ensure data matches on all required items

4. Create 'URNs' on subsets of the full data fields (i.e. not all columns) as required to, for example, locate data matches in order to fill in missing items on unmatched fields if all other fields match

Summary

1. Data Structure is important - generally it should consist of headings on the top row, and contiguous data (no spaces, blanks, sub-headings)

2. The above format allows this raw data to be effectively filtered, sorted, totalled amongst other uses

3. Converting to this type is possible with a little thought on how you might restructure your current data, without losing the extra information it has, as shown in the example where we retained the splits on months yet converted to a format that is usable for various excel tools

4. Using Totals that cover the entire range and an extra row means you can add additional rows safely and keep your totals and other formula ranges working as expected

Summary:

1. Vlookup and Hlookups should not have hard-coded column/row numbers where possible, as any additional columns or rows added will return the wrong results and you may not even be aware

2. Building in robust parameterised feeds into the column / row number, e.g. via cell references or formulas relying on cell references is the best approach

3. Use INDEX and MATCH in combination to avoid the extra potential work required to maintain VLOOKUPs/HLOOKUPs that may still rely on manual upkeep of certain data - this avoids potential issues right at the source by creating a robust formula

Project - Data Dashboard

1. Download the file attached to this lecture below, and work your way through the tasks

2. There are hints viewable for each task on the sheet itself - change font color to view

3. Good Luck!

Project - Data dashboard Walkthrough

The best way to approach this video, if you got stuck in your own attempt, is to watch that task’s solution only within this video, and ideally only as much as it takes to get the spark or hint you need to carry on yourself.

To this end you can find timestamps for each task’s solution below the video to jump to as required.

When you’re done, then by all means watch the entire thing!

Timestamps

0:45 Task 1

1:25 Task 2

3:02 Task 3

6:14 Task 4

8:03 Task 5

8:37 Task 6

10:21 Task 7

11:58 Task 8

13:24 Task 9

14:47 Task 10

20:47 Formatting and presentation

Well done! Course Complete!

Be sure to share the course with anyone else you think might benefit.

Best Regards,

Aamir