Video Lesson: Beginner’s Introduction to Excel

One video that I have been meaning to do is a lesson focused on those of you who have never (or rarely) used Excel, and needed an introduction to the program and how it works.  I’ve created this lesson in an attempt to introduce the most basic information about what Excel is and go over some examples of what it could be used for.

Disclaimer: If you’re pretty familiar with Excel, this video may be extremely boring.  You have been warned!

Throughout the lesson, I explain the following information and questions in great detail:

– What am I looking at when I open Excel for the first time?

– Explanation of Column / Rows and how referencing works.

– Navigating Excel, knowing where you are in the spreadsheet, and the ‘name box’.

– Overview of sheets including adding, deleting, and renaming.

– Explanation of hard-coded values vs. formulas/functions, and Excel default cell formats.

– Formatting options including bold/underline, alignment (e.g. centering), and creating borders.

– Information about the formula bar, and how to use the ‘Insert Function’ feature.

– Example of creating a simple personal finance tracker and how to input information into Excel.

– Introduction to the Master Workbook, including the function listing and previews of examples from other lessons.

Feel free to show this lesson to anyone who wants to learn Excel but doesn’t know the first thing about getting started!

Now if you’re following the Lesson Guide, you should move on to Learning Basic Formatting Techniques.

VBA PO Generator: Homework Solution Part 1

The following is a guest video lesson provided by Chris H. (reddit: Kristofmic).

In this video series we’ll follow along with solutions to the advanced homework for the VBA Purchase Order Generator Walkthrough.

In Part 1 of the homework solution, we develop enhancements to the purchase order worksheet enabling users to input as many entries into the purchase order as they would like, as well as dynamically selecting and removing items that have been added to the purchase order.

You will learn how to do the following with Visual Basic (VBA) in Excel:
  • Inserting and deleting rows & columns using the .EntireRow / .EntireColumn and .insert / .delete functions
  • Expanding a selection of cells using the .resize() function
  • Moving to various cells in the worksheet using the .offset() function
  • Copying and pasting values and formatting using the .copy and .pastespecial functions
  • Additional Usage of For…Next loops

Here is the updated Purchase Order Generator file to follow along with the lesson.

Video Lesson: Basic Excel Formatting Techniques

Today’s video lesson goes through some of the basic formatting techniques in Excel 2010:

– How to Use the Auto-Fill Drag Handle / Fill Series Options

– How to Use the Format Painter

– Freeze / Unfreeze Panes

– Sheet Formatting (Coloring Tabs / Renaming)

– Print Area Setup / Print Options

Download the latest Master Workbook to follow along with the video!  The sheet titled “8 – Autofill” in the Master Workbook is what is used in the lesson example:

Now that you’ve got the basic formatting techniques down, why not learn about Conditional Formatting!

Guest Excel Lesson: Introduction to Tables

User VertexVortex has been helping users out on r/Excel for quite a while.  After reaching out to him on Reddit, he agreed to help out by creating a lesson of his own specifically for this site.  Here’s his guest lesson on Structured Tables.  Hope you find it useful!!

The following is original content provided to Excel Exposure by VertexVortex:

This is a basic introduction to the benefits of Excel’s Structured Tables (added in version 2007). The basic premise of Structured Tables (referred to as Tables for the rest of this post) is that the most common format of organized data in Excel should be easier to use. Here are just a few of the benefits to these Tables.

Let’s say I have a collection of data, with similar pieces of information being placed in the same columns, and each row giving information about other pieces of data in that row. If you’re into database lingo, the column is a field, the rows are records, and the pieces of data are tuples. If you’ve worked much with Excel at all, you’ve probably run into data organized in this fashion.

I want a sum of the movement, but I don’t know how many rows I’m going to have. I could edit the formula every time, or I could insert rows (which would extend my formula), I could write the formula to extend beyond my current data and hope it doesn’t ever extend beyond…

But none of these are good options. This is where the Tables come in.*  It’s very quick to implement. Click on the Insert ribbon, and select Table.

*(There is another option, called dynamic named ranges. Dynamic named ranges take a bit more setup and are more trouble to work with, but are still a viable option, especially for earlier versions of Excel.)

The dialog will guess your data range, and ask if your data has headers. If your data doesn’t have headers, you should add them if you can.

The references in Tables can work a bit differently. *

To reference all the data rows of the whole table: TableName
To reference the whole table (including headers): TableName[#All]
To reference a column: TableName[ColumnName]
To reference a column header: TableName[[#Headers],[ColumnName]]
To reference a column with the header: TableName[[#All],[ColumnName]]
To reference an entire table row from outside the table: TableName[@]
To reference a data point from the same row (from outside the table): TableName[@ColumnName]
To reference a data point from the same row (from inside the table): [@ColumnName]

*(These reference syntax are true in 2010. In 2007, replace [@] with [#ThisRow]).

Here’s how we solve our expanding data problem:

Notice that the formula doesn’t change despite the number of items in the table increasing.

Fortunately, this is not the only benefit of using Tables. Tables are very dynamic, and that translates very well into other common Excel tasks. Let’s take a look at charting for a second. Normally if we have something charted, and we add more data, we have to go in and fiddle with the data ranges so that they match our data.

In the same way that formulas do not need to change when referencing certain sets of data in a Table, the data references of charts also do not need to be edited when new rows are added. If I take the data from earlier, and create a quick chart (deleting the annoying “Date” series, as I didn’t want to chart that), I can see my categories and their Movement. Adding another category into my Table automatically adds the points to my chart. You can see the effect here, before I’ve had a chance to key fake data in (which will also automatically update).

Another common tool in Excel, Pivot Tables, is made easier by using Tables. If you click anywhere inside your Table, then click “Insert->Pivot Table”, the reference will automatically fill in with the reference for your Table (defaults to Table1).

Question: Since it’s not a cell reference, does that mean the data will update dynamically if the size of the Table changes?

Answer: Of course it does!

All we need to do is refresh the Pivot Table when our source data changes, this will update both the content and size of our data.

Now, in my opinion, one of the neatest things about Tables is the calculated column. Calculated columns kick in when you enter a formula into a blank column cell in a Table, or in a column that’s already a calculated column. When the formula is entered, Excel automatically copies the formula to every cell in the extent of the Table. As rows are added or deleted, formulae are created and removed. The references react just as if you had copied and pasted the formula by hand, so it’s easy to plan for your absolute/relative reference concerns.

And while we’re on the subject, don’t forget the special Table referencing discussed earlier. In this example, I’m calculating the month of the date in the Date field:

This is the result immediately after hitting Tab:

Here’s more special Table referencing in action:

Isn’t that easier to read?  And the results immediately after hitting Enter:

So remember when I mentioned that Pivot Tables would update with both the content and size of our Table? If you go back to the Pivot Table that I’m going to pretend that you’ve made, and click Refresh, the new columns will pop up.

Before:

   

After:

This doesn’t make the Pivot Table better, but it sure does make it easier to use.

So that’s just a few of the many benefits of Excel’s Tables.

Some notes for advanced users:

Excel creates a Table by default when certain data is imported through the Data ribbon. This removes some of the pains of data that changed size as it imported. Of course, this only works in versions later than 2003.

For VBA programmers interested in using Tables, the collection of Tables in a worksheet are accessed by

                Workbook.Worksheet.ListObjects

And if the Table is the result of a data import, the query table can be accessed by

                Workbook.Worksheet.ListObjects(index).QueryTable

Thanks to VertexVortex for spending time creating this lesson for Excel Exposure!

Video Lesson: Formula Auditing & Evaluation

Formula Auditing and Formula Evaluation are powerful components in Excel, and today’s video lesson goes over the following uses of the Formula Auditing features:

Show Formulas:  How to change view to show formulas instead of cell results.

Formula Evaluation:  See how Excel views the formula as you walk your way through the calculations.

Trace Precedents / Trace Dependents:  Helps finding out where your data is coming from and/or going to.

Watch Window:  Allows for user to set certain cells into a window that can be viewed as you switched sheets.

Make sure to download the most recent Master Workbook to follow along.

Video Lesson: Advanced Lookup – Index & Match

The versatility and usefulness of the Index and Match functions are cherished by Excel veterans, but many less experienced users have either not encountered them, or are a bit confused by the usage of the two.

This Excel Video Lesson shows you how you can use the Index & Match functions to do advanced lookups which can be more reliable and versatile than just VLOOKUP or HLOOKUP alone.  In the video I go over the following uses of Index & Match:

– Two-Way Lookup (using both a row header and column header to find the intersecting value).  This can be very helpful when columns and rows may be added or deleted to your ranges.

– Averaging entire row/column information based on a lookup value.

– Summing entire row/column information based on a lookup value.

Make sure to download the most recent Master Workbook so that you can follow along at home!