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: 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.