Excel Video Tutorials: A Comprehensive Guide
Did you know that every Excel spreadsheet has more than 16,000 columns and a million rows? Did you know that you can create shapes in an Excel chart? Are you familiar with VLOOKUP, COUNTIF or pivot tables?
If you’re looking to become the office guru on Excel but it’s become equal parts intriguing and perplexing, don’t worry. You’ve come to the right place.
Below you’ll find 22 Excel video tutorials – categorized into basic and intermediate levels – to help you expand your knowledge on Excel formulas, navigation and spreadsheets.
Basic Excel video tutorials
You’ll learn: The basics of Excel including: active cells, formulas and the formula bar, how to undo and remove, and how to select a range of cells.
TIP: Use the keyboard shortcut CTRL+F1 to minimize or restore ribbons.
You’ll learn:To insert comments into an Excel spreadsheet. This skill is particularly helpful when working on one cell or working on a report. There are a few options to show or hide comments or to make the comment box large or small.
TIP: You can respond to a comment within a spreadsheet by clicking on the comment window, and then clicking on NEW COMMENT.
You’ll learn:To find and replace a function in Excel; the tutorial demonstrates how to select a cell and replace existing text with any changes you’d like to make.
TIP: To find asterisks, question marks or a tilde character in a spreadsheet, click the FIND & SELECT tab, and then in the FIND WHAT box enter your search data.
You’ll learn:To build a GRAPH in Excel by selecting columns, finding the type of graph you would like to use, manipulating and customizing it and editing your original data.
TIP: To insert a line break, click on the place where you want to break the line and press ENTER.
You’ll learn: To create simple equations by building a list and finding the AVERAGE, SUM, MAXIMUM and MINIMUM of those formulas.
TIP: If the SHOW A ZERO IN CELLS box has a zero in the check box, and the EXCEL OPTIONS dialog box is checked, empty cells are not counted, but zero values are.
You’ll learn:To find a value. Specifically, you’ll learn to highlight a field in the spreadsheet (e.g., A14-A24) and find a given value within that field.
TIP: The “V” in VLOOKUP stands for vertical. Use VLOOKUP for comparison values located in columns of the data that you want to find.
You’ll learn:To count the value and to build CONCATENATE formulas. All of these functions will help you merge, categorize and organize lists.
TIP: You can use the ampersand (&) sign instead of the CONCATENATE function to join text items.
You’ll learn:To open up a blank worksheet and be able to link information from a separate worksheet to your new worksheet. This is helpful if you want to use identical data in separate projects.
TIP: In addition to linking to another sheet, you can reference a cell on another spreadsheet.
You’ll learn: To create a new pivot table within a spreadsheet. Pivot tables are helpful for condensing large amounts of data into smaller sections – think states into regions.
TIP: Find the PIVOT TABLE and PIVOT CHART command on the ribbon (main menu) and there you will also find INSERT TAB, TABLE GROUP.
You’ll learn: To resize columns, add cells and columns and change the size of a cell.
TIP: To apply a custom word art style, select TEXT FILL, TEXT OUTLINE or TEXT EFFECTS.
You’ll learn: To work with reports. The tutorial specifically talks about sales reports, creating a new rule and formatting cells based on value.
TIP: When creating a new workbook, use CONDITIONAL FORMATTING on cells that are referenced in another worksheet.
You’ll learn: To take a basic Excel spreadsheet and connect it to access database so you have an SQL database.
TIP: Fields within a spreadsheet will accept null values if its required property is set to NO and its VALIDATION RULE doesn’t prevent null values.
You’ll learn: The quick way to build the MACROS formula is by using the red macro button. You’ll learn some of the basic routines of the MACROS formula.
TIP: To duplicate macro actions, hold down the CTRL key and drag the action(s) to the location in the macro where they need to be copied to.
Intermediate Excel video tutorials
You’ll learn: The basic structure of a formula. There’s an equals sign, a cell reference and the ability to divide, multiply, add or subtract within that formula to create the formula you want.
TIP: If you want to verify that a cell contains a numeric value and not text, use the formula =IF(CELL(“type”, A1) = “v”, A1 * 2, 0).
You’ll learn: In this intermediate tutorial, learn how to use a formula called EQUALS COUNT. The formula is there to count how many values or numbers exist within a specific range.
TIP: If you want to count logical values, text or error values, use the COUNTIA function.
You’ll learn: To view the average value within a range of cells or columns. You’ll be able to calculate things like average cost by simply typing in the formula for EQUALS AVERAGE.
TIP: The AVERAGE function measures the central tendency of a set of values. The three most common measures of central tendency are: average, median and mode.
You’ll learn: Proficiency in finding the MINIMUM, MAXIMUM or the SUM of a field of cells or columns.
TIP: The MAX function returns the largest value in a set of values. The MIN function returns the smallest number in a set of values. The SUM function adds all numbers specified as arguments.
You’ll learn: To use the formula builder. Functions included in the formula builder include: AUTO SUM, AVERAGE, COUNT NUMBERS, MINIMUM and MAXIMUM, RECENTLY USED, HYPERLINKS, COUNT, MAX, SUM, STANDARD DEVIATION and more.
TIP: To delete an array formula, select the whole formula, (e.g., =C2:C11*D2:D11), press delete and then CTRL+SHIFT+ENTER.
You’ll learn: To combine, arrange and organize large amounts of data. This is particularly useful for doing inventory searches.
TIP: To view the number as a percentage, select the cell and click CELLS on the FORMAT menu.
You’ll learn: To find the value of an item and what other details are included for that item, use the VLOOKUP function.
TIP: If the RANGE_LOOKUP argument returns as true or is omitted, the values in the first column of TABLE_ARRAY should be placed in ascending order or VLOOKUP may not return the correct value.
You’ll learn: To lock a table or value into place. After watching this tutorial, you will be able to update a value quickly to reference cells and formulas.
TIP: To prevent replacing existing data when dragging the fill handle, be sure the ALERT BEFORE OVERWRITTING CELLS check box is selected.
You’ll learn: To work with reports at an intermediate level. The intermediate level tutorial specifically talks about sales reports, creating a new rule and formatting cells based on value.
TIP: If cells in a range contain a formula that returns an error, conditional formatting is not applied to those cells. To apply conditional formatting, use an IS or IFERROR function to return the value.
So, did these tutorials spark a flame in you? Are you looking forward to impressing your friends with your new found knowledge of Excel?
For your reference, here’s an alphabetical list of functions in Excel.
If these Excel video tutorials piqued your interest, be sure to also check out the Choosing a College guide for further information on areas of study, college costs and location.