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: 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: Math and Statistical Functions

This Excel video lesson is about the Experience Level 1 Math and Statistical Functions in Excel.  Here is the level 1 function list extract from the Master Workbook that goes along with the video lesson.

The formulas discussed in the video are as follows:

MIN – MAX – AVERAGE – MEDIAN – SUM – PRODUCT – SUMPRODUCT

COUNT – COUNTA – COUNTBLANK – ABS – COUNTIF – COUNTIFS

SUMIF – SUMIFS – AVERAGEIF – RAND – RANDBETWEEN

ROUND – ROUNDDOWN – ROUNDUP – SUBTOTAL

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.

Next up if you’re following the Lesson Guide is learning about Date & Time Functions (e.g. TODAY, MONTH)

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

Video Lesson: Lookup / Reference Functions

As listed in the Master Workbook (see link at the top of the page), there are functions grouped into categories. This video is a demonstration of the Experience Level 1 – Lookup & Reference functions.

The functions covered in this video lesson are:

ADDRESS  — COLUMN — COLUMNS — HLOOKUP  — INDEX — INDIRECT

LOOKUP — MATCH — OFFSET  —  ROW  —  ROWS  —  VLOOKUP

Extract of Level 1 – Lookup and Reference Functions from the Master Workbook:

A good follow-up lesson to check out would be either the Advanced Vlookup Examples or Advanced Lookup – Index & Match lessons.

Video Lesson: Absolute & Relative Referencing

Absolute and Relative Cell Referencing

Note: This video was originally the 3rd lesson in the plan, but due to the comments regarding it being too confusing at that level, I have moved it to the Intermediate section effective 3/23/17.

This video tutorial focuses on Absolute vs. Relative cell referencing within Excel.  You likely know it as the dollar sign ($) used in functions/formulas (for example, C5 vs $C$5).

There is also a quick demonstration of the video using a VLOOKUP formula.  The VLOOKUP formula is explained in more detail in these two lessons here and here, but the example in this video gives a basic understanding of how it works in Excel.

Update 5-22: I’ve noticed some users (especially those with Macs) have had difficulty viewing this video.  Since it is one of the older videos, I will be re-recording it and adding information so that it is comparable to more recent videos.

If you’re following the Lesson Guide, next up is Math & Statistical Functions (e.g. SUM, AVERAGE)

Excel Training Lesson Plan Posted

I just updated the site with a proposed lesson plan to give everyone an idea of the topics that I plan on covering throughout the course.

I may jump around between topics depending on what makes more sense and what people are interested in, but I will update the Lesson Plan page to reflect any new posts.

Excel Video Lesson: Text Functions

This video tutorial is a demonstration of the Experience Level 1 Text functions.

Make sure to download the newest version of the Master Workbook that I updated earlier today to follow along.

The functions covered in this lesson are:

CONCATENATE — EXACT — FIND — LEFT — LEN — LOWER — MID — PROPER
REPLACE — RIGHT — SEARCH — TEXT — TRIM — UPPER

Extract of Level 1 – Text Functions from the Master Workbook:

Text Functions marked as Level 1

Next video lesson is Logical Functions and Formulas.

Any feedback is appreciated.

Excel Video Lesson: Date & Time Functions

As listed in the Master Workbook, there are functions grouped into categories. This video is a demonstration of the Experience Level 1 Date & Time functions.

The functions covered in this video lesson are:

DATE — DATEVALUE — TODAY  — DAY  — MONTH — YEAR
TIME — TIMEVALUE — NOW — HOUR — MINUTE — SECOND

CORRECTION: I mention in the video that Excel views dates as the number of days since January 1, 1900 but it is actually January 0, 1900 (a bit confusing since January 0 doesn’t make intuitive sense).

Now that you’re an Excel ninja when it comes to Date & Time functions, try mastering Text Functions (e.g. LEN, CONCATENATE)

Welcome to Excel Exposure!

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