Tag Archives: Advanced Excel Tutorial

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!

As always, make sure to download the latest Master Workbook to follow along.

EDIT: The Gross Profit formula should not be including the ‘Retail Price’ in it’s SUM function. I’ve left this mistake in there so that the numbers remain the same in the video & file, but I just wanted to point out that the calculation is incorrect.

VBA Macro Walkthrough

One of the best ways of learning VBA is to take a look at completely functioning macros & code to see how others approach a problem. In this video lesson, I walk you through a Purchase Order Generator that I created using Excel and VBA. Throughout the video, the following topics are discussed:

– How to create unique userforms which allow users to input information in a separate window.

I’ve created this site as part of the University of Reddit course (this information is posted here as well). The goal is to expose the world to the amazing functionality of Microsoft Excel. I hope to make this site an easy reference for Excel users of all skill levels so that they can refine their skills, become more efficient, and get the most out of this amazing piece of software.

Excel Exposure Training Course – General Overview

This course will be a complete lesson / teaching tool related to Microsoft Excel. It will have sections and information applicable to all experience levels. It will be updated regularly with additional lessons, tips, and demonstrations of how you can get the most out of Excel.

This course will be taught using Microsoft Excel 2010, but most of the functionality can be found in Excel 2007/2003 (although it may be in a different location). I will not be focusing on the Mac version of the product as I do not have it.

Prerequisites

General knowledge of Microsoft Excel is recommended, but there will be sections of the site for complete beginners.

These will likely be updated last because the main purpose is to improve the knowledge-base of those already familiar with Excel. I will try to make sure it is clear what the experience level should be for each lesson.

Syllabus

The course will be hosted on this website. I will be posting a new video lesson from the Lesson Plan weekly on Sunday nights (as well as quick tips and other informative links sporadically throughout the course).

The general outline of the syllabus will evolve as the lessons are created/posted. Always check the Lesson Plans for updates to the course schedule.

Teacher qualifications

Masters of Business Administration (MBA) with concentration in Accounting

Bachelor’s Degree in Accounting

Over 8 years of experience pushing Excel to the limit and improving my abilities.

Questions?

Feel free to reach out to me with any questions, comments or feedback: ben@excelexposure.com

About This Site

Excel Exposure is a free online Excel training class designed to help you learn more about the features and functionality of Microsoft Excel 2010. The site includes video lessons, tutorials and information to help improve your Excel skills.