Mr. Spreadsheet’s Favorite Excel 2010 Tips & Tricks

Video Training Course, Deluxe Edition
3.8 (8 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
1,132 students enrolled
$35
Take This Course
  • Lectures 52
  • Contents Video: 2.5 hours
    Other: 8 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 6/2013 English

Course Description

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.

What are the requirements?

  • All Internet browsers, including Internet Explorer 9 or higher, Safari, Chrome, and Firefox

What am I going to get from this course?

  • 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

What is the target audience?

  • Beginner to intermediate Excel users

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Basic Excel Usage
05:44

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

03:27

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. 

03:43

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.

04:54

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.

07:38

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

03:01

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

02:40

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.

01:48

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.

00:55

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.

Section 2: Data Entry
04:17

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.

02:12

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.

03:16

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. 

01:16

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.

02:47

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.

Section 3: Formatting
03:39

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.

03:33

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.

01:40

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.

00:59

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.

01:15

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.

03:07

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.

Section 4: Basic Formulas and Functions
04: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.

03:37

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.

01:09

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.

02:46

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.

01:35

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

02:22

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.

03:30

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.

02:26

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.

02:14

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

03:21

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. 

Section 5: Useful Formula Examples
03:18

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

02:41

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.

02:38
This video describes how to create a “live” calendar in a range of cells.
02:47

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. 

03:10

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.

05:49

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.

Section 6: Conversions and Mathematical Calculations
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.

Section 7: Charts and Graphics
01:31

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.

01:04

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

01:52

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.

00:53

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. 

01:55

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.

01:12

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.

03:35

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.

01:58

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.

Section 8: Data Analysis and Lists
04:34

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. 

02:59

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

Section 9: Working with Files
02:16

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.

02:57

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.

Section 10: Printing
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. 

Section 11: Mr. Spreadsheet Bookshelf Personal Library
200 pages

John Walkenbach's Favorite Excel 2010 Tips & Tricks consists of a series of non-trivial tips and tricks that cover all aspects of Excel. Tips are improved ways of maximizing the power of Excel to create robust applications. Tricks are shortcuts that will speed up application development with Excel.

John's favorites include tips and tricks on dealing with function arguments, creating "impossible" charts, pivot tables, taming the Ribbon bar, using UserForms, creating add-ins, absolute vs. relative references, changing data entry orientation, overcoming the 7-level nesting limit, dynamic chart data, sorting more than three columns, entering fake data for testing purposes, custom functions, and much more.
John Walkenbach's Favorite Excel 2010 Tips & Tricks, Part 2
282 pages

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Founded in 1807, John Wiley & Sons, Inc. has been a valued source of information and understanding for more than 200 years, helping people around the world meet their needs and fulfill their aspirations. Wiley and its acquired companies have published the works of more than 450 Nobel laureates in all categories: Literature, Economics, Physiology or Medicine, Physics, Chemistry, and Peace.

Wiley is a global provider of content and content-enabled workflow solutions in areas of scientific, technical, medical, and scholarly research; professional development; and education. Our core businesses produce scientific, technical, medical, and scholarly journals, reference works, books, database services, and advertising; professional books, subscription products, certification and training services and online applications; and education content and services including integrated online teaching and learning resources for undergraduate and graduate students and lifelong learners. Wiley's global headquarters are located in Hoboken, New Jersey, with operations in the U.S., Europe, Asia, Canada, and Australia. The Company is listed on the New York Stock Exchange under the symbols JWa and JWb.

Ready to start learning?
Take This Course