Mr. Spreadsheet’s Favorite Excel 2010 Tips & Tricks
4.0 (15 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,448 students enrolled

Mr. Spreadsheet’s Favorite Excel 2010 Tips & Tricks

Video Training Course, Deluxe Edition
4.0 (15 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,448 students enrolled
Created by John Wiley & Sons
Last updated 6/2013
English [Auto]
Current price: $23.99 Original price: $34.99 Discount: 31% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 2.5 hours on-demand video
  • 2 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Customize the Ribbon and Quick Access Toolbar
  • Understand conditional formatting features
  • Work with formulas and functions
  • Discover the different types of data
  • Create Sparkline graphics
  • Add images to your charts
  • Protect your spreadsheet and avoid errors
  • All Internet browsers, including Internet Explorer 9 or higher, Safari, Chrome, and Firefox

Let Mr. Spreadsheet welcome you to the wonderful world of Excel with this information-packed video training and e-book course

In Mr. Spreadsheet’s Favorite Excel 2010 Tips & Tricks Video Training Course, Deluxe Edition, you’ll gain valuable insight to essential spreadsheet tools and techniques to help you make Excel 2010 more efficient than you ever imagined. This one-of-a-kind collection of Excel video lessons covers everything from transforming data without using formulas to customizing the Ribbon, importing a text file into a worksheet range, using images to display your data, and other new and innovative ways to put Excel to work for you. You’ll also get a special edition of John Walkenbach’s Favorite Excel 2010 Tips & Tricks e-book filled with over 200 easy-to-follow Excel hints, timesavers, and secrets to take your spreadsheet skills to the next level. Whether you’re a longtime Excel enthusiast or just getting started with spreadsheets, this course has something for you. So, if you’re ready to start your journey to Excel excellence, Mr. Spreadsheet is ready to show you the way.

Who this course is for:
  • Beginner to intermediate Excel users
Course content
Expand all 52 lectures 10:21:19
+ Basic Excel Usage
9 lectures 33:50

Using the Ribbon is straightforward enough, and in this video, you’ll learn about the different tabs and their uses.

Preview 05:44

One of the new features in Excel 2010 is called Protected View. Although it may seem like Excel is trying to keep you from opening your own files, Protected View is all about protecting you from malware. Learn the Protected View basics in this video so you can keep your files safe. 

Preview 03:27

Many Excel users think that the only way to select a range of cells is to drag over the cells with the mouse. Although selecting cells with a mouse works, it’s rarely the most efficient way to accomplish the task. A better way is to use your keyboard to select ranges.

Preview 03:43

If you find that you continually need to switch Ribbon tabs because a frequently used command never seems to be on the Ribbon that’s displayed, this video is for you. The Quick Access toolbar is always visible, regardless of which Ribbon tab is selected. After you customize the Quick Access toolbar, your frequently used commands will always be a click away.

Customizing Your Quick Access Toolbar

Learn how to customize the Ribbon for your specific needs and uncover those options that Excel just won’t let you use.

Customizing The Ribbon

Discover how to create more professional-looking documents with Office document themes.

Using Document Themes

Excel has various options that enable you to hide elements in order to customize your work-space. In some cases, you can change the display options in more than one place. In this video, I show you the easiest Hide method possible.

Hiding User Interface Elements

If you have data in a column or row that you don’t want to see, you can hide the column or row. Doing this is often useful if you have formulas that provide intermediate calculations and you don’t want them to appear in a report. Or, you may just want to hide unused rows and columns so that you can focus only on the used area of the sheet.

Hiding Columns Or Rows

Excel makes it easy to convert a range of cells into a picture. The picture can either be a dead image (it doesn't change if the original range changes) or a live picture (which reflects changes in the original range). The range can even contain objects, such as charts or shapes.

Taking Pictures Of Ranges
+ Data Entry
5 lectures 13:48

When you type something into a cell in a worksheet, Excel goes to work and makes decisions regarding the type of data you entered. If you understand how Excel interprets the data you enter into a cell, you can save yourself a bit of frustration when Excel’s decision about what you entered doesn’t correspond to what you had in mind.

Understanding The Types Of Data

A common type of worksheet contains a table of data with descriptive headings in the first row. But, as you scroll down the worksheet, the first row scrolls off the screen so you can no longer see the column descriptions. A feature introduced in Excel 2007 eliminated this age-old spreadsheet problem — but it only works if your data is in the form of a table.

Keeping Titles in View by Freezing Panes

Excel is capable of speaking to you. You can have this feature read back a specific range of cells, or you can set it up so that it reads the data as you enter it, but it’s not the easiest tool to find. Fortunately, the feature is still available — you just need to spend a few minutes to make it available. 

Proofing Your Data With Audio

Normally, Excel watches you type, and if it looks at all like you’re typing an e-mail address or a Web URL, the entry is converted into a hyperlink. Sometimes that capability is helpful, but sometimes it’s not. Learn how to control what you type so that it doesn't automatically turn into a hyperlink with this video.

Controlling Automatic Hyperlinks

Most Excel users probably believe that a VBA macro is required in order to display a drop-down list in a cell. But it’s not. You can easily display a drop-down list in a cell — no macros required.

Creating A Drop Down List In A Cell
+ Formatting
6 lectures 14:13

When you enter a date into a cell, Excel formats the date by using the system short-date format. You can change this format by opening the Windows Control Panel and selecting Regional Settings to help you make your data look the way you want.

Using Custom Date and Time Formatting

Conditional formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells. Microsoft significantly enhanced conditional formatting to include several visualization features: data bars, color scales, and icon sets.

Understanding the New Conditional Formatting Features

Merging cells is a simple concept: Join two or more cells to create a larger single cell. See how using this feature can enhance the appearance of your worksheet.

Merging Cells

Excel cell formatting isn't an all-or-none proposition. In some cases, you might find it helpful to be able to format individual characters within a cell.

Formatting Individual Characters in a Cell

If you need to enter a lot of text into a cell, you have two choices – allow the text to spill over into the adjacent cell or allow the text to wrap. This video will show you how you can use the Wrap Text feature to keep your spreadsheet neat.

Wrapping Text In A Cell

One of the most underused features in Excel has been named styles. Named styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles helps to ensure a consistent look across your worksheets.

Using Named Styles
+ Basic Formulas and Functions
10 lectures 27:21

If you have a range of cells that contain formulas, you can quickly convert these cells to values only (that is, the result of each formula), and this common operation is easier than you may think.

When To Use Absolute References

Mixed cell references aren’t used often, but as you see in this video, in some situations, using mixed references makes your job much easier.

When To Use Mixed References

If you have a range of cells that contain formulas, you can quickly convert these cells to values only, which will be demonstrated in this video.

Converting Formulas To Values

Often, you have a range of cells containing data that must be transformed in some way. This video describes how to perform addition, subtraction, multiplication, and division on a range of values without using any formulas.

Transforming Data without Using Formulas

This video illustrates the versatile method of transforming data by using temporary formulas.

Transforming Data by Using Formulas

A common type of spreadsheet model contains input cells (which are changed by the user) and formula cells that work with those input cells. If you want to delete all the values in the input cells but keep the formulas intact, here’s a simple way to do it.

Deleting Values While Keeping Formulas

Sometimes a formula returns an error, such as #REF! or #DIV/0!. Usually, you want to know when a formula error occurs, but in some cases you might prefer to simply avoid displaying the error messages.

Avoiding Error Displays In Formulas

After learning how to provide a meaningful name to a cell or range, you can use those defined names in your formulas, but there’s a trick to understanding the right way to use names in Excel.

Understanding the Secret about Names

This video describes a useful technique that can remove some clutter from your worksheets: named constants.

Using Named Constants

According to Excel 2010, the world began on January 1, 1900. If you work with historical information, you may have noticed that Excel doesn’t recognize pre-1900 dates. To be able to sort by dates that precede 1900, follow the instructions in this video. 

Working With Pre 1900 Dates
+ Useful Formula Examples
6 lectures 20:23

Discover the DATEDIF function, which calculates the difference between two dates and expresses the result in terms of months, days, or years.

Using The DATEDIF Function

The Excel pivot table feature is incredibly powerful, and you can often create pivot tables in lieu of creating formulas. This video describes a simple problem and its various solutions.

Using a Pivot Table Instead of Formulas
This video describes how to create a “live” calendar in a range of cells.
Displaying A Calendar In A Range

Suppose that you update a worksheet frequently by adding new data to its columns. You might need a way to reference the last value in a particular column, and this video shows you how. 

Returning the Last Nonblank Cell in a Column or Row

The VLOOKUP and HLOOKUP functions are useful if you need to return a value from a table (in a range) by looking up another value.

Looking Up An Exact Value

A two-way lookup identifies the value at the intersection of a column and a row. This tip describes two methods to perform a two-way lookup.

Performing A Two Way Lookup
+ Conversions and Mathematical Calculations
1 lecture 01:29

You know the distance from New York to London in miles, but your European office needs the numbers in kilometers. What’s the conversion factor? The Excel CONVERT function can help.

Converting Between Measurement Systems
+ Charts and Graphics
8 lectures 14:00

An Excel chart consists of a number of different elements. For example, all charts contain a plot area and at least one data series. A chart can also contain elements such as a chart title, axes, data labels, and so on. To work with a particular chart element, you need to select it — something that is often easier said than done.

Selecting Elements In A Chart

Learn to easily incorporate a pattern, texture, or graphical file for elements in your chart with this video.

Using Pictures In Charts

Oddly, Excel doesn't provide a direct way to convert a chart into a stand-alone graphics file, such as a GIF or PNG file. In this video, you’ll learn different methods for saving an Excel chart as a graphics file.

Saving A Chart As A Graphics File

If you need to save a range as a graphic image, the best approach is to use one of several screen capture programs that are available. This type of software makes it easy to capture complete windows, or just a portion of a window. 

Saving A Range As A Graphic Image

Normally, an Excel chart uses data stored in a range. Change the data, and the chart is updated automatically. Usually, that’s a good thing. But sometimes you want to “unlink” the chart from its data range to produce a static chart — a snapshot of a chart that never changes.

Freezing A Chart

When you copy a chart as a picture, it opens the door to some creative effects that are possible with pictures, but not with charts, which you’ll discover in this video.

Creating Picture Effects With A Chart

One of the new features in Excel 2010 is Sparklines graphics. A Sparkline is a small chart displayed in a single cell. A Sparkline lets you quickly spot time-based trends or variations in data. Because they

are so compact, Sparklines are often used in a group.

Creating Sparkine Graphics

Excel lets you place quite a few different types of objects on a worksheet: charts, shapes, clip art, and SmartArt, for example. To work with an object, you must select it. The easiest way to select a single object is to click it. What if you want to select multiple objects? Watch this video to find out.

Selecting Objects On A Worksheet
+ Data Analysis and Lists
2 lectures 07:33

This video describes how to use tables to eliminate common errors and make common tasks much easier to do while making the results much better looking. 

Using The Table Feature

This video describes a quick method for creating a frequency tabulation for a single column of data.

Creating a Quick Frequency Tabulation
+ Working with Files
2 lectures 05:13

If you need to insert a text file into a specific range in a worksheet, you might think that your only choice is to import the text into a new workbook and then to copy the data and paste it to the range where you want it to appear. However, you can do it in a more direct way.

Importing a Text File into a Worksheet Range

This video describes three ways to capture data contained on a Web page – pasting a static copy of the information, creating a refreshable link, or opening the page directly within Excel.

Getting Data From A Web Page
+ Printing
1 lecture 01:29

If you want your printout to display one or more fixed rows at the top and/or one or more fixed columns along the left, learn how to do it in this video. 

Displaying Repeating Rows or Columns on a Printout