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.

Video Lesson: Information Functions in Excel

In the following lesson I go through the Experience Level 1 Information Functions.  These functions are used to obtain information about formatting & referencing about specific cell or range references.  In this lesson I review the following functions:

ISBLANK – ISERROR – ISTEXT – ISNONTEXT – ISNUMBER

as well as the following info_types of the CELL() function:

“address” – “row” – “col” – “contents” – “width” – “filename”

“color” – “prefix” – “protect” – “type” – “format”

The CELL(“format”) info_type has many code results, so I’ve summarized them here:

Make sure to download the latest Master Workbook to follow along with the video 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!

Video Lesson: Advanced Pivot Tables – Round 1

Pivot Tables are extremely useful in Excel.  This Advanced Excel video lesson is a follow-up to the Introduction to Pivot Tables and covers the first round of advanced topics.

The lesson goes over some additional Pivot Table options and features including:

– Drill-down functionality which creates a summary sheet of any Pivot Table amount selected.

– Pivot Table Options including show/hide totals, error/empty cell display options, and refresh when opening workbook (and more).

– Value calculation options including sum/count/average, % of grand total, and % of parent row / parent column total.

– Formatting / display options including row/column label layout, sorting labels, and other formatting.

As always, download the latest version of the Master Workbook to follow along at home.

Feel like you’re starting to master the Pivot Table?  Move on to Advanced Pivot Tables – Round 2 to continue learning more!

Alternative to Merged Cells (Quick Tip)

Typically, many users are drawn to the Merged Cells feature in Excel, despite it having some significant disadvantages (especially related to copy/paste of ranges).

Frequently, Merged Cells are used in areas to group cells together visually, which can be done differently without causing the issues that merging does.

You can use an option within formatting called ‘Center Across Selection’ which takes the text from the leftmost cell of a range and centers it across the selected range.

Here is a screenshot of how to find it / use it by right clicking on a range and going to ‘Format Cells’.  Then click the Alignment tab as shown below (the Center Across Selection option):

Voila! The text is centered across multiple cells without the need for merging them.

You can also remove the gridlines in Excel to make it look a bit cleaner.

That option can be found here under the ‘View’ tab:

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!

PDF to Excel – Free Online File Conversion

Most of us have encountered situations where we have PDFs that need to be converted to Excel.  This free online resource has worked very well for the PDFs I have used.  Some specific points worth mentioning:

– Documents up to 30mb can be uploaded for conversion.

– Web-based so there is no installation of computer software (requires email address).

– According to their Privacy Policy, emails and documents are kept confidential.

FYI: This is an external website which I do not control or operate and should be used at your own risk.  Please let me know if you have any issues.

PDF  to Excel Conversion

VBA Walkthrough #2: Message Encoder & Decoder

This VBA macro is hopefully a bit more interesting than the last, and shows how I created a message encryptor/decryptor in Excel using VBA/macros.

The lesson covers these specific areas of VBA usage:

– For…Next loop (including nested loops)

– If…ElseIf…Then Statement

– Excel functions: CHAR() / LEN() / VLOOKUP()

– VBA functions: Mod / Mid / Asc / Chr

Download the Message Encryptor & Decryptor here to follow along with the video lesson.

VBA Walkthrough #1: Purchase Order Generator

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.

– Usage of VBA userform toolbox items such as Label, ListBox, TextBox, and Buttons.

– Coding related to specific events (when an item in a list is chosen, or a button is clicked).

– Declaring variables, and using worksheet functions in VBA.

– Populating Purchase Order sheet with relevant product information based on user’s selections.

Download the Purchase Order Generator workbook to follow along.

Also, this is the first video lesson with HOMEWORK!!  Please feel free to complete the assignments in the worksheet and send to ben@excelexposure.com for feedback.

 

See the Guest Video Lesson of the Homework Solution here!

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!

Video Lesson: Conditional Formatting in Excel

Conditional Formatting can be very useful when presenting data in a spreadsheet, or for allowing information to jump out to you easily.

In the following video lesson I go over the following uses of it:

– Formatting based on numerical values (greater than, less than, between, etc).

– Formatting based on text contained in cells.

– Quick overview of Data Bars, Color Scales, and Icon Sets

– How to use custom formulas with Conditional Formatting to highlight entire rows or other cells (extremely useful / versatile).

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

Correction: The ‘Conditional Formatting’ option usually is located under the ‘Home’ tab and not the ‘View’ tab as referenced in the video.  That is due to my custom group that I have on my home tab.  To learn more about customizing your ribbon (aka the top menu area), see my Five Powerful Excel Tips video.  Sorry for any confusion, and thanks to Elizabeth in the comments for pointing it out!

Video Lesson: Introduction to Macros & VBA

Using macros in Excel requires knowledge of Visual Basic for Applications (VBA).  In this video lesson you will get an introduction to how macros work within Excel and how to build them.  The video lesson includes the following topics:

– Setting up Excel workbooks to use Macros & VBA (filetype, menu and security settings).

– How to record a macro and edit recorded VBA code.

– How to write custom macro including InputBox and MsgBox functions.

– How to assign buttons and shortcuts to macros.

Make sure to download the Visual Basic Examples workbook here to follow along.

Video Lesson: Introduction to Pivot Tables

Pivot Tables are an extremely useful way of analyzing large sets of data quickly and easily in Excel.

This video lesson provides a beginner’s introduction to Pivot Tables.

Download the most recent Master Excel Workbook to follow along.


Now that you know the basics, it’s time to get your hands dirty with Advanced Pivot Tables – Round 1

Video Lesson: Advanced Filtering in Excel

Using auto-filters in Excel provides a useful way to drill down and extract data from spreadsheets.

This video lesson explains multiple ways you can use filters in Excel.

Make sure to download the most recent Master Workbook so that you can follow along.

Next in the Lesson Guide is Creating and Designing Charts (including Sparklines)

Video Lesson: Logical Functions and Formulas

This Excel video lesson is about Logical Functions and Formulas in Excel.  Here are the formulas that I go through in the video lesson:

Logical formulas are extremely useful in more advanced Excel spreadsheets/methods and they are a great tool to have at your disposal.

Make sure to download the most recent Master Excel Workbook so that you can follow along. The spreadsheet I’m reviewing is in the Function Examples sheet in that Excel file.

The formulas covered in this video lesson are:

IF – IFERROR – AND – OR – TRUE – FALSE – NOT

Thanks for watching and feel free to leave any questions or comments in the comment box below!  I will respond via email if you include it with your comment.

Quick Tip: Remove Duplicate Values from List

Many times there are instances when you’ll have a large amount of data and you’ll want to be able to extract a listing of one specific column with only one line for each item type (for example, you may have a listing of all 2,500 customer addresses with a country field and may want to know the full list of countries represented by the customers in the listing).

Here’s a quick and easy method I use to extract the information (and this method is also a small introduction into using logical formulas, which will likely be the next video).  Using an IF function and some sorting, you can extract a listing of information without the duplicate values.

Here’s a screenshot with instructions for how to do it (click to see enlarged version):

Remove Duplicate Values

Bonus HW assignment: Try to think of other ways that you may be able to get a similar listing without using my method above.

Excel Video Lesson: Data Validation

This Excel video lesson walks you through how to set up Data Validation rules.  The main purpose of Data Validation is to set up rules so that only certain types of information can be entered into cells (which helps to reduce manual entry error).

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

Let me know if you have any questions!

Excel Add-In: ASAP Utilities

Hey Everybody,

Here is a really nifty add-in that I recently found (free for personal use, one-time fee for businesses).  It’s called ASAP Utilities.

Some examples of important features in the add-in:

–       Cell selection based on content, formatting, etc [examples: select all cells between 50 and 250 or all bold cells or all red cells]

–       Advanced sorting [examples: font color, bold, formula, text length, etc]

–       Apply formula/calc to selections [example: add “/24” to the end of all formulas, or change formulas in bulk, copying formulas without moving references]

–       Easily export/import files [Excel, TXT, database, web, create CSV and delimited files]

–       Vision Control [allows for bulk editing of formatting across entire workbook]

–       20+ Additional Excel functions/formulas

Here’s a full list of features by category.

I recommend giving it a spin; it has loads of interesting/useful features and can be especially useful for beginners since it has an easy interface.

Please leave a comment to let me know of any specific sections of the lesson plan that you’re looking forward to.  I appreciate your patience with the videos!

– Ben

Excel Course: Book and Website Recommendations

I wanted to suggest a few books that I’ve used in the past which are extremely helpful in learning Excel.  I’d recommend checking any one of these books out as they have lots of useful information and examples for how to get the most out of Excel:

Excel for Dummies 2010 (All-in-One)

Excel 2010 Bible

Excel 2010: The Missing Manual

Excel 2010: Power Programming with VBA

The last book is specifically related to Macros and involves programming with VBA (Visual Basic for Applications).  I would recommend any of these as self-teaching tools for doing additional learning outside of my lessons.  Many of these have earlier 2007/2003 counterparts which would be good if you have that version of Excel.

Additional Excel Online Training Resources

– Educational: Chandoo

– Educational: OZgrid

– Educational: Chip Pearson

– Educational: Mr. Excel

Educational: Excel Easy

– Help: Microsoft Online Answers Site

– Excel Templates: Vertex42’s list of free Excel templates, spreadsheets, and calculators.

Non-Excel Related Websites

Orlando – Web Design and WordPress Support.