Excel 2016: IFS Function

The following post was created by Kasper Langmann as a guest post for Excel Exposure.  If you’d like to see more of his work, check out Spreadsheeto for more Excel tutorials.

Follow along with the lesson via this workbook that he created specifically for this article.

How To Use the IFS Function

With the release of Excel 2016, Microsoft included several new functions. One of those new functions is the ‘IFS’ function. The ‘IFS’ function builds upon the ‘IF’ function in such a way that it simplifies one of the most common uses of the ‘IF’ function.

In order to have a discussion about the ‘IFS’ function, it would be a good idea to review the basics of the ‘IF’ function. Both functions are logical functions which means that they return a value based on whether some logical test is TRUE or FALSE. For instance, we might want to know if a number is less than 10. In this case, the ‘IF’ function is designed such that if a given number is less than 10, its output is a TRUE result. If a given number is not less than 10, then the output of the ‘IF’ function is a FALSE result.

Excel also allows us to combine multiple ‘IF’ functions into the same formula for a more complex needs. This is the feature that ‘IFS’ improves upon. But more on all that in a bit.

The basics of the ‘IF’ function

Now that we have a bit of background about the ‘IF’ function, let’s look at the syntax:

‘=IF(logical_test,[value_if_true],[value_if_false])’

The items inside the parentheses are what we refer to as arguments. As you can see, the ‘IF’ function has three arguments. The first argument is fundamental to the function. It is the logical test by which we are seeking a TRUE or FALSE result.

In our previous example, our ‘logical_test’ is ‘5 < 10’ if the number we were testing was ‘5’. For our next two arguments, we can choose what we would like for the function to return in the case that the number we are testing is actually less than 10. In this case, we will keep things simple and actually use “TRUE” and “FALSE”as results (we could have easily chosen “Yes” and “No”, or “1” and “0”, etc.) .

So now let’s take a look at this in an actual worksheet:

picture-1

This is the example we just discussed and as you can see in the formula bar, we have chosen the text strings “TRUE” and “FALSE” for our ‘value_if_true’ and ‘value_if_false’ arguments, respectively. Note that if we use a string for these arguments, they must be enclosed in double quotes.

The IF function can be combined with several other functions to increase its functionality. See this video on how to use the functions: IF, IFERROR, OR, AND, TRUE, FALSE and NOT.

Now let’s take a quick look at an example of two ‘IF’ functions in the same formula, often referred to as nested ‘IF’ statements:

picture-2

Take special notice that the second ‘IF’ function actually serves as the ‘value_if_false’ argument of the first ‘IF’ function. Based on our previous example, it should be clear that if the given number we are testing is less than 10, this nested ‘IF’ formula would output ‘less than 10’ and be done. However, in this case, where 11 is the number being tested, the formula is set up so that the false result for the first ‘IF’ test is now the second ‘IF’ test and it will now result in a TRUE result, or ‘less than 20’.

The nested ‘IF’ idea can be expanded up to seven ‘IF’ functions. While that can be powerful functionally, it’s not difficult to imagine how that could become unmanageable at some point from a testing and troubleshooting perspective.

Experts have devised all kinds of workarounds to the nested ‘IF’ function for many years due to its limitations. However, with the release of Excel 2016, Microsoft has now come up with a better solution: the ‘IFS’ function.

How to use the ‘IFS’ function in excel

The ‘IFS’ function is Microsoft’s answer to the nested ‘IF’ function. It allows for the same functionality in a simpler form designed for multiple conditional tests.

The ‘IFS’ function essentially tests multiple conditions until the first true is returned. This may make more sense by looking at the syntax:

‘=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …)’

So let’s see how this works with the previous scenario:

picture-3

Note the contrast of the formula here compared to the previous example using a nested ‘IF’ formula. For starters, the ‘IFS’ function is a far more straightforward method with improved readability. It is a very intuitive method of achieving the same results.

One thing that might not be obvious at first with the ‘IFS’ function is that its lack of an argument for a FALSE result to any of the logical tests needs to be considered. For instance, in our example, if we were to instead choose the number 21 as our test value, we will get an error if the formula is otherwise left unchanged:

picture-4

So in order to address this, we simply add a last logical test that is always TRUE (like ‘1 = 1’) and select the value we want the formula to return in the event that all previous logical tests turn out to be FALSE.

picture-5

A practical example

Now that we have covered the basics of how to use the ‘IFS’ function, let’s apply it to a real-world example. Let’s consider a situation in which we have a measurement for hand size and we want to convert that to glove sizes. The following table shows our conversion from the measurement to the size.

picture-6 

So the concept here is that the recommended size for hand measurements 6.5 or less is ‘XXS’, 7 or less is ‘XS’, 7.5 or less is ‘S’, and so on. These statements are our logical tests and resulting values if TRUE. And if we have a short list of measurements, we can use the cell references in our arguments to match up to sizes based on the measurement conditions:

picture-7

Note the similarity to our more basic example earlier. This is a great illustration of the ‘IFS’ function at work in a real life type of scenario. We can also replace the literal values in our formula with the cell references in our cross reference table (found two pictures above in the range of A2 to D2):

picture-8

The ‘IFS’ function is just one of the many new features available in Excel 2016 and we have just shown how useful it can be. It builds on one of the most widely used logical functions providing an even broader range of flexibility. With its simpler approach to tackling the needs only nested ‘IF’ functions could previously address, the ‘IFS’ function figures to become a mainstay for even the most inexperienced Excel user. Read more on the use of the IF and IFS functions here.

This guide is written by certified Microsoft Office Specialist Kasper Langmann from Spreadsheeto. Did you like this guide? Then go check out his free Excel training.

Excel Modeling World Championships 2014

ModelOff 2014

Invitation to the Excel Modeling World Championships 2014 from ModelOff.com

We’d like to invite members of the ​Excel Exposure community to the annual Excel and Financial Modeling World Championship 2014 Event (ModelOff, www.modeloff.com ). The Advanced Excel educational competition helps celebrate Excel in Financial Services. The fun, challenging and innovative competition has a mission to inspire skill development with Microsoft Excel, Financial Modeling and Financial Analysis which is central to global businesses and communities. The competition showcases some of the fastest, hard-working and talented Excel minds from 100+ countries. Round 1 starts on 25th October 2014 (8 weeks away)

Summary of Event

Over 3,000 participants competed in the ModelOff 2013 event. Major Global Partners and Sponsors are: Microsoft, Intralinks, S&P Capital IQ, Kaplan Education, Bloomberg, AMT Training, Corality and Ernst & Young.  Participants come from diverse companies and jobs – such as Analysts, Associates and Managers at Investment Firms and Accounting Firms, CFOs, Analytics Professionals, In-House Excel Gurus and Consultants with a shared passion for Microsoft Excel and Finance.  Students comprise ~35% of all entrants worldwide – most studying Commerce, Accounting, Finance and Masters university qualifications. The countries most represented have typically been: United States, UK, Poland, Russia, Canada, Australia, India and Hong Kong.  Female participation is ~20% of all competitors – hopefully higher this year (the reigning champion is Hilary Smart 26yo from London).

How It Works

The ModelOff competition involves two online qualification rounds (2 hours each) conducted simultaneously around the world. The Top 16 performers are flown to New York for the Live Finals at the offices of Microsoft and Bloomberg in early December 2014.  Questions are mostly case study and multiple choice format – with some ranging from a basic understanding of discounted cash flow (DCF) analysis, 3-way integrated cash flow models to more complex project finance and simulation techniques.  Some basic Accounting, Finance and Excel knowledge is likely needed to progress to Round 2.

Free Training and Preparation

All past questions and tests from ModelOff 2012 and 2013 are free and available on the ModelOff website. The organizers believe in accessible excellence and this can be a great starting point for anyone looking to become involved for the first time and improve their Advanced Excel skills in Financial Services and Financial Modeling.  We actively encourage all participants to visit all our community partners, bloggers, our major global sponsors during and following the event for their own learning, mentoring and professional development. Competitors in the Top 10% of ModelOff 2014 will be eligible for exciting local and international opportunities, offers from community partners and fun learning experiences (e.g. Trips to Microsoft Excel in Redmond). We’re also hosting free networking events in major Financial Centres including Hong Kong, London, Sydney, New York and Regional Meetups in the coming months for anyone interested in networking and mentoring with Excel-users in their local cities. Entry to the competition is $20 for students and $30 for professionals.

2013-Finals-v2

John R Persico is one of the lead organizers of the ModelOff event. John can be contacted at john@modeloff.com.  Get involved at: www.modeloff.com

Infographic – Benefits of Learning Excel

Why should you learn Excel?

SkilledUp.com was nice enough to share this infographic (below) with Excel Exposure.  It details some good reasons for why Excel can be a powerful tool to learn to use.

If you’re on the fence about learning Excel, take a look and see how it can benefit you.

Feel free to leave comments below with anything you think they’ve missed.

career value of learning excel infographic skilledup

This infographic was originally published on SkilledUp.com.

VBA Important Tips

Thanks to Nick Williams at Acuity Training for this guest post!

The lesson will go over the following tips:

 

1) Loop Through Code

2) Variable Scope

3) Error Checking

4) Immediate Window

 

VBA Tip #1 – Loop Through Code

A common event in programming is having a section of code which needs to be executed one or more times, this is achieved using a Loop.  There are two types of loops available in VBA:

For … Next Loop – use this when you know how many times the code needs to be executed.

Do … Loop – use this when you need to test how many times the code needs to be executed.

For … Next Loops

When you know the number of times a piece of code needs to be executed, use the For … Next loop, this uses a counter to determine how many times the code has looped at any given time.  The following example shows how a For … Next Loop could be used to enter a value in a set of cells:

Image 1

The value of  i is incremented by 1 each time the Next line of code is executed.

Do Loops

Most occasions in programming we are not sure how many times a piece of code needs to be executed, and in most cases we would test this against a condition, ie. repeat until all records have been formatted, since we cannot determine how many records there may be at any given time, this would be a good example of performing a Do … Loop.  The Do … Loop has several formats it can take on, the following table explains the variations of the loop:

Image 2

The following example shows how a Do … Loop could be used to format a set of cells, the variation is a Do Until and the condition is until the Active Cell does not contain a value:

Image 3

Image 4

VBA Tip #2 – Variable Scope

Variables are constantly used in any programming language to capture and temporarily store values; VBA is not exception to this.  However, the importance of using variables is ensuring you set the scope for the variable correctly.  The Life of a variable is known as its scope:

Image 5

The following code example displays how we would use Procedure, Module and Global scope variables:

Image 6

Image 7

VBA Tip #3 – Error Checking

Once you have tried and tested your programs, the next step is to apply some Error handling which will make as many checks as possible during initialization to ensure that run time errors do not occur later.  If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box.  This may be acceptable, but a more user friendly message may be more desirable. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method.

There are generally three types of Errors:

1.   Compiler errors, such as undeclared variables that prevent your code from compiling

2.   User data entry error such as a user entering a negative value where only a positive number is acceptable

3.   Run Time errors, these occur when VBA cannot correctly execute a program statement.

The more checking you do before the real work of your application begins, the more stable your application will be.

Image 8

Image 9

 

VBA Tip #4 – Immediate WIndow

The Immediate Window within the VBA Editor is used as a scratchpad window where statements, methods and Sub procedures can be evaluated immediately.  Very useful when you want to see the results of a line or two of code, but do not want to run the entire procedure.

Image 10

Image 11

Hope you enjoyed the post, and thanks again to Acuity Training for creating this lesson!

Excel 2013: Real World Examples of New Functions

Real World Examples of Excel 2013’s New Functions

[This is the second article I’ve written for the Microsoft MVP blog, but figured I would post it here as well for those who might find it handy]

Microsoft included over 50 new functions with Excel 2013, and I wanted to walk you through a few real world examples of these new additions. You’ll soon be able to see how handy these extra tools are in your ‘Excel toolbox’. I’ll only be going over just a select few of the new functions, but feel free to leave a comment with some of your other favorite new functions or features!

FORMULATEXT()

Until Excel 2013, there has been a gap in our ability to see and use formulas in any given cell. There has been the option of going into Formula Auditing mode (either via the ribbon or Ctrl + `) which allows you to view all of the formulas in a spreadsheet. However, this is an all-or-nothing type feature and only applies to the visualization of the spreadsheet, allowing for switching between all formulas being displayed, or having all results/values show instead. Sometimes there are instances where you want to be able to track or assess a formula in another cell without having to select it directly or change viewing modes entirely. The FORMULATEXT function allows for this ability.

For example, you could very quickly check if two formulas are identical across different spreadsheets, even if their resulting values are not the same. This might be useful if you have a spreadsheet template that you’ve setup and you want to make sure that two versions of it have identical formulas. By comparing the formula text from each of the sheets, you could quickly see if any changes had been made.

Let’s say that you keep a weekly record of daily sales for your company, with Monday through Sunday sales being tracked in cells B6:E12 of multiple spreadsheets, as seen here:

3823-image_5371c0bd

If you were using a third ‘Sum’ spreadsheet to add up the Monday-Sunday totals from multiple sheets, you might want to make sure that the formulas in the cells are the same. Depending on how you’ve designed your workbook, knowing that the formulas haven’t changed could help ensure that the template is working as intended and that any associated formulas would reflect the correct information. In this example I use a ‘Check’ worksheet with formulas to see whether the formula text in each of the order total columns is the same. Here I’ve used the new FORMULATEXT function to compare whether the formulas are the same in column E, and I’ve also put additional FORMULATEXT functions in column G to help illustrate the formulas I’m using to compare the two sheets:

1643-image_3c633f81

I’m using a direct comparison by putting the = sign between the two sheet/cell references. This comparison results in a TRUE because the formula text in the Week 1’s cells is the exact same as in Week 2’s. If I were to go in and delete row 3 from above the table in one of the sheets, the function would give me a FALSE on all of them, showing me that the formulas no longer match and something has changed with my underlying data sheets. I could go further and put a conditional format to alert me visually when any of my values turn false by using a COUNTIF statement like this =COUNTIF(E6:E13,FALSE) which would always show 0 unless a FALSE sprung up in my ‘Check’ sheet. Overall, the addition of the FORMULATEXT function certainly adds to your ability to track formulas in cells and understand changes in your workbooks.

ISFORMULA()

The new ISFORMULA function joins it’s brethren of ISBLANK, ISNONTEXT, ISNUMBER and the other information functions related to checking what kind of value the output is. In this case, however, the function allows for us to see whether or not the referenced cell contains a formula. Before this addition, it would require a lot more effort and a much more complicated formula to check whether a cell has a formula in it. Using the previous example above, if we wanted to track whether or not the weekly totals were a formula vs. something else (like a hard-coded value), we could use the ISFORMULA function to check. First, here’s a glimpse of the sheet for Week 1 sales once I’ve changed cell E7 to be a value only. I’ve included the Formula Text in column G which comes up with an #N/A value for the total that I changed:

8561-image_15b4b334

Now instead of checking out the formula text in column G, I’ll change it to an ISFORMULA function, so that you can see the result. I’ve conditional formatted the FALSE result so that it stands out. This would be another good way of keeping track of which components of your spreadsheet are formulas vs. hard-coded values (and can help you identify issues with your calculations):

5758-image_5fcfbe19

Especially when using the conditional formatting, the changed cell really jumps out at you, whereas before it might have been a lot more difficult to realize that E7 has been changed to a value. If you had gone into Tuesday’s sales to update from 3 to 4, the order total would not appropriately update. Unfortunately, without some kind of formula/error tracking, it would likely go unnoticed. If you find yourself having trouble with how errors work in Excel, or ideas for how to track changes in your data, see my tutorial on Error Checking & Data Monitoring.

XOR()

When dealing with logical functions in Excel, they require a statement that results in a TRUE or a FALSE (aka Boolean) value. The AND function will assess whether all of the logical statements passed to the function are TRUE, and the OR function will check to see whether any of the values show up as TRUE. In Excel 2013, Microsoft has added the XOR function which is an ‘Exclusive OR’ function. In the simplest version of 2 logical statements, XOR will return TRUE if that the result is TRUE for either of the two results, but not both and not neither. When more logical statements are added, the XOR works so that it will result in a TRUE if the total number of TRUE inputs is odd, and FALSE is the number of TRUE inputs is even. Here is an example which shows all of the possibilities for 3 logical statements being fed to the XOR function (I’ve highlighted the TRUE/FALSE values to make it a bit easier to understand):

1145-image_0631a165

It may be a bit difficult to grasp the concept at first, or how it could be applied to a real-life scenario. The term is of ‘Exclusive Or’ is more frequently heard when related to programming or computer science in general, but here’s an example of how the XOR function could be used to assess whether an employee is working a half-day using only two logical statements:

7345-image_2c9384b0

As you can see, it only shows up as TRUE for working a half day is one of the two statements in columns C & D are true. There are much more complicated ways you can use XOR, especially related to mathematics, but hopefully this gives you a taste of how the function works.

Brush up on your Functions!

I’ve only gone through only three of the new functions here, but if any of these piqued your interest, I’d suggest reviewing all of the new functions that have been added with Excel 2013, and certainly would suggest taking some time to review the 400+ functions that you already had at your disposal before these new functions were added. Since there are so many aspects of Excel, you might find a hidden gem that you didn’t know already existed! For a listing of pre-existing functions, shortcuts, and examples of how to use them, feel free to check out my Master Workbook. Hope you’ve enjoyed the lesson and feel free to leave any comments or questions below.

Excel 2013 Feature: Excel Data Model

[This is the article I wrote for the Microsoft MVP blog, but figured I would post it here as well, with the example files, for those who might find it handy]

PivotTables have certainly become one of the most powerful ways to quickly and easily transform data into information. Yet there are still many Excel users who, for one reason or another, find themselves avoiding PivotTables. Well, Microsoft has made a lot of strides over the years to introduce new functionality and options to make PivotTables easier and more intuitive to use. With Excel 2013, Microsoft has introduced many new PivotTable features, including the new Excel Data Model which takes PivotTables to a whole new level!

You can download the example workbook to follow along (but it requires Microsoft 2013).

Here’s the original Excel file to start with, and here’s the completed version.

Turning Data into Information

One of the limitations of PivotTables has been the fact that you could only use information from one data table, requiring any additional information to be added into the table in order to be included in the PivotTable. In many instances, you won’t have a lot of control over the format of the initial data or the fields provided, especially when this is pulled into Excel from some other software. For example, you may have Travel Expenses for employees that you’ve extracted from your accounting system that looks something like this:

8814-clip_image001_5f18ff84

This is a perfectly fine range of data, and you could certainly make a PivotTable based off of it, but there are a few issues. Unless you’re very familiar with your company’s internal coding, the ‘Dept Code’ and the ‘Employee Type’ fields likely won’t provide you with too much information. Especially if the output is being provided to someone without extensive systems knowledge, you’ll need to convert these codes into understandable values to provide the most clear and coherent information (see Figure 1-2 below for the related lookup ranges).

5102-clip_image002_4b8b9fe3

In prior versions of Excel, you would likely use a VLOOKUP function (or some other convoluted method) to add additional columns to bring in the associated Department or Employee Level. Doing this introduces more room for error (based on the quality of the formula), and is a strain on calculation speed and memory.

With the introduction of the Excel Data Model, you can create relationships between columns of related information in separate tables. Using this new functionality, we can bring in the related Department/Employee information by adding the related Dept Code and Employee Type tables to our Data Model. This is a very simplistic usage of the Data Model, but there are many additional ways you can utilize it to your advantage (especially when incorporating PowerPivot/PowerView).

How to Create an Excel Data Model

Now I’ll walk you through the process of taking our Employee Travel Expenses and creating a data model with the relevant ranges.

First, you must convert your data ranges to tables. To do this, simply highlight the range you want to convert, go to the Insert tab, and click on Table:

7774-clip_image003_4b1f6cee

Once you’ve converted all three ranges to Tables, you can change the name of each Table so that you can easily identify them by going to the Design tab once you have a table selected (I’ve named them: ExpenseTable, DeptTable, EmployeeTable):

6116-clip_image004_3f1d7cba

Now we will add all three of our tables to the Data Model by going to Connections under the Data tab, and clicking Add to the Data Model:

1854-clip_image006_77c826c7

Making a PivotTable using your Data Model

After adding these tables, you will now see ThisWorkbookDataModel, which can be now be used to create our PivotTable! Go to the Insert tab, and choose PivotTable. Then choose the option ‘Use an external data source’ which allows you to choose our Data Model as the connection:

7536-clip_image007_1e2a0a13

Once you’ve selected the Data Model as your data source for the PivotTable, you should be able to see all of your tables in the field list. Also, notice the sleek new look of PivotTables in Excel 2013, including that little box with the gear logo in the top right which allows for many different views/options to be changed quickly and easily:

5582-clip_image008_1d51a429

Now, we can start adding fields from each table to one of the PivotTable sections below. When we add information from more than one table, Excel will prompt us to create a relationship. If I want to see the Employee Expenses by Employee Level, I will need to create a relationship between the Employee Type found in both of the tables.

8168-clip_image009_7cca64761

And once you click OK, you’re ready to rock! As you can see above, I’ve already added the Employee Level for the Columns, with the Employee Name in the Rows, and their Expense Amount in the Values section. Therefore, I should get a nice PivotTable that shows me the breakdown of expense spending across Employee Levels (with a little bit of additional changes to make it look prettier):

3872-clip_image010_403265d9

Feel free to play around and see which other ways you can slice and dice the data once you add in the Dept Code relationship. Of course, this is only a simple example of how you can use the Data Model. It becomes much more powerful if you have multiple tables with lots of data, and can even include connections to other workbooks or data sources (like Access for example). Once the setup of the Data Model and relationships are complete, you can slice and dice your data very quickly and easily without having to use complicated in-between formulas. Hope you’ve enjoyed the lesson!

Video Lesson: Error Checking & Data Monitoring

Error Checking and Data Monitoring in Excel

In the following video tutorial, I go over the following items in detail:

Explanation and example of major error types in Excel:

#DIV/0 — #VALUE! — #REF! — #NAME? — #NUM! — #N/A — #NULL!

Overview of Excel’s built-in error checking options and functionality.

How to use “Go To Special…” to find errors easily.

Copy / paste large data areas for data monitoring.

Using direct comparison, and SUM/COUNT functions to compare data changes.

Use of Conditional Formatting for emphasis when comparing ranges.’

Download the Error and Data Checking file to follow along with the video lesson!

Excel 2013 In-Depth Preview

What’s new in Excel 2013!

Microsoft has made it possible for anyone who’d like to check out Excel 2013 (included with the full Office 2013) through their Excel 2013 pre-release trial here.

UPDATE 2/20: Now they have the Office 365 full version for a one month trial!

I’d recommend downloading the Excel 2013 example file I go over in the video, but the majority of the information is included in these two links (if you are unable to download the Excel trial):

Excel 2013 New Function Examples // Excel 2013 All New Functions Listing

The video lesson shows some of the great new features in Excel 2013 including:

– Quick overview of appearance, template, and file menu changes [00:13]

– The new Flash Fill functionality [02:01]

– Quick-Analysis features including charts, tables, totals, formatting and sparklines [03:06]

– New charting features like Recommended Charts, and chart editing options  [05:02]

– In-depth examples of the following new functions [08:10]:

DAYS – ENCODEURL – FORMULATEXT – IFNA – ISFORMULA

ISOWEEKNUM – NUMBERVALUE – SHEET – SHEETS

– Example workbook includes Pivot data and tables already created and setup for trying out the new Excel Data Model that allows for multi-table Pivot Tables.

If you like what you’ve seen so far in Excel 2013, you should check out my Excel Data Model functionality tutorial.

Video Lesson: Advanced Pivot Tables – Round 2

Advanced Pivot Tables Round 2

This lesson picks up right where the last Pivot Table lesson left off.

In this tutorial, you will learn:

  • How to use multiple levels of data within row or column labels.
  • Expand / collapse all functionality through right-click (Expand / collapse entire field)
  • Advanced filtering options including: right-click, row label, column label, and report filters. Includes multiple simultaneous filters, and using search/filter functionality within each data level.
  • ‘Defer Layout Update’ option which allows for changing fields without pivot table automatically updating.
  • Fields Section / Areas Section Stacked vs. Other Pivot Field List Formats.

Make sure to download the Advanced Pivot Tables – Round 2 workbook to follow along with the lesson!

If you’re following along with the Lesson Guide, next up is Auto-Filter and Advanced Data Filtering

Excel Video Lesson: Advanced VLOOKUP Examples

Advanced Vlookup Examples

The VLOOKUP function is one of the most frequently used functions in all of Excel.  In this video lesson I go over some advanced usages of the VLOOKUP function including:

– In-depth overview of the VLOOKUP syntax:

= VLOOKUP ( lookup_value, table_array, col_index_num, range_lookup ).

– Examples of how to set up intelligent formulas which pull information based on other cells rather than typed in (aka hard-coded) values.

– How to set up an intelligent order query which pulls a full row of information about different orders using one formula that gets copied and pasted.

Download the Advanced VLOOKUP Examples workbook to follow along with the video.

Video Lesson: Creating and Designing Charts

Excel has very powerful charting capabilities, which many users do not take full advantage of. This lesson goes over a few of the types of charts using some example data.  Here is a quick summary of what is in the lesson:

– How to use the Insert Chart feature for Line, Pie, and Column charts.

– Formatting/editing chart axes, data labels, legends, data series, and chart titles.

– Detailed introduction to Sparklines (which allows for charts within individual cells).

Make sure to download the Chart Lesson workbook to follow along with the video!!

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: Five Powerful Excel Tips

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

I’ve noted the time that each section starts in the video, but you may want to let it load completely first as I’ve had errors skipping around early.

1) Dynamic Named Ranges [0:13]

As a follow-up to the Named Ranges video, I go through some examples which have been partially modified from the Dynamic Named Ranges post on OzGrid.  Definitely check out that link for additional information about how to use the functionality!

Keyboard Shortcuts: New Named Range is ALT + MMD, and the Name Manager is ALT + MN.

To check what your named range is currently referencing, hit F5 (for Go To) and type in the dynamic named range into the reference field, and it will highlight that range.  This is useful since dynamic named ranges do not appear in the Name Box.

2) Find and Select -> Go To Special… [5:28]

An excellent feature in Excel is the Find and Select functionality, which includes the ability to select a certain subset of a range.  For example, you can easily select all cells within a range that contain: Blanks / Formulas / Visible Cells / Constants / Conditional Formats and a whole lot more…

Keyboard Shortcuts: For ‘Go To Special’, hit F5 and then ALT + S.

3) 3D Formulas & Editing – Formulas Across Multiple Sheets [7:32]

Do you have information that is spread across multiple sheets in the same format? For example, you may have the same sheet template with different monthly/weekly/daily information.  Using ‘3D formulas’ you could sum a certain cell or range across multiple sheets by indicating the starting and ending sheets (and that formula will grab everything in between as well).

Keyboard Shortcuts: Selecting multiple tabs is CTRL + SHIFT + Pg Up or Pg Dn.

4) Text to Columns [11:24]

Have a lot of data that’s jammed into a single cell?  You can use Text to Columns to separate that cell’s contents into multiple columns based on the location of spaces, commas, periods or other character(s) that you specify.  An example would be splitting a cell with full names into a first name and last name column based on the space.

Keyboard Shortcuts: ALT + DE brings up the Text to Columns option.

5) Customizing Ribbon Menu Tabs & Menu Groups [13:08]

There is a lot of customization that you can build into Excel so that you have custom menu tabs & groupings to help you get to the Excel features and functionality that you use most.

You may have noticed my ‘Ben’ grouping in previous videos on my ‘Home’ tab which has PivotTable, Name Manager, Freeze Panes, Data Validation, Conditional Formatting and other features that I use very frequently.  In this video I will show you how you can do the same!

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: 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 proper Cell Referencing.

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!