Use Excel Like A Pro. Fast.
4.6 (581 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.
3,009 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Use Excel Like A Pro. Fast. to your Wishlist.

Add to Wishlist

Use Excel Like A Pro. Fast.

Go from spreadsheet rookie to rockstar
4.6 (581 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.
3,009 students enrolled
Created by Kieran Luke
Last updated 7/2016
English
Price: $50
30-Day Money-Back Guarantee
Includes:
  • 2 hours on-demand video
  • 7 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I Learn?
Ability to design and build well structured models from scratch
An understanding of how to use what functions, when
Mastery of time-saving keyboard shortcuts
View Curriculum
Requirements
  • None
Description

This two-hour Excel crash course is the quickest way to go from spreadsheet rookie to rockstar. It's an accelerated 'learn-by-doing' approach: we'll solve a business problem by designing a model, collecting data and building a spreadsheet. Along the way, we'll cover the keyboard shortcuts, functions and insider 'best practices' to make your spreadsheets sizzle!

Who is this for?

Anyone who has to build Excel models from scratch. If you fall into one of these groups, this course will suit you particularly well:

- Business professionals (consultants, analysts, marketers etc)
- Students using Excel to analyze data or entering business careers
- Entrepreneurs who use Excel to run their startups, to analyze opportunities and to present to investors

What makes this course different?

- It's practical. The instructor has worked in consulting for eight years. That's a lot of Excel. His practical knowledge of how to use Excel in real life, as well as pitfalls and traps to avoid, will save you hundreds of hours of spreadsheet anxiety

- It's fast; only two hours. And we believe the quality bar is higher than any other course out there (leave a review to let us know if we are right!)

- It uses the 'case-method' pedagogy. By teaching in the context of a real example, retention is significantly higher than a traditional 'formula-led' approach

Discounts?

Check out Kieran's blog for coupon codes

Who is the target audience?
  • For students going into business careers and professionals / entrepreneurs seeking to improve their excel skills
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 15 Lectures Collapse All 15 Lectures 02:07:03
+
Designing and setting up your model
3 Lectures 13:11

This chapter sets the scene, explaining the problem we are tasked to solve. In the rest of the course we will solve the problem together.

Preview 01:01

In this chapter we cover two concepts:
- Start by clarifying the problem you are solving
- Design your model on paper before building it in Excel

Also, download the course files below. They contain:
- Keyboard shortcut guide (for PC and Mac)
- Raw data files
- An index of topics covered

Develop your model blueprint (and download course files)
04:11

Download the "Sales data.xlsx" file to start building your model.

In this chapter we cover:

Concepts:
- Always include an "About This Spreadsheet" tab in your model
- Color coding is critical for good spreadsheet hygiene

Operations:
- Save as
- Insert tab
- Rename tab
- Recolor tab
- Resize columns
- Wrap text
- Remove gridlines
- Insert rows

Shortcuts:
- Alt+F+A (save as)
- Shift+F11 (insert tab)
- Ctrl+PageUp (move to tab to-the-left)
- Ctrl+PageDown (move to tab to-the-right)
- Ctrl+B (bold cell)
- Alt+H+F+G (increase font size)
- Alt+H+F+K (decrease font size)
- Alt+H+W (toggle wrap text on/off)
- Alt+W+V+G (toggle gridlines on/off)
- Ctrl+C (copy)
- Ctrl+V (paste)
- Ctrl+Shift+Plus(+) (insert rows)
- Ctrl+S (save)

Preview 07:59
+
Moving fast and looking good
1 Lecture 09:47

In this chapter we cover:

Operations:
- Freeze panes
- Autofilter

Shortcuts:
- Ctrl+Arrow Key (move to edge of data)
- Shift+Arrow Key (highlight cell)
- Ctrl+Shift+Arrow Key (highlight block of data)
- Ctrl+Shift+Plus (+) (insert rows)
- Ctrl+Minus (-) (delete rows)
- Ctrl+B (bold text)
- Alt+H+K (comma format for numbers)
- Alt+H+9 (decrease decimal)
- Alt+H+0 (increase decimal)
- Alt+W+F+F (freeze panes)
- Alt+A+T (toggle autofilter on/off)
- Alt+A+C (clear autofilters)

Roam around without a mouse and make data presentable
09:47
+
Importing data and cleaning text
1 Lecture 16:59

Download the "Product-author data.txt" file to follow along in this section.

In this chapter we cover

Concept:
- Keep up your color coding of cells

Functions:
- Concatenate
- Proper
- Upper
- Lower
- Trim
- Left
- Right
- Mid
- Len
- Search

Operations:
- Get external data
- Text to columns
- Drag and copy formulas
- Ctrl+A (select all)
- Ctrl+PageUp (move to tab to-the-left)
- Ctrl+PageDown ((move to tab to-the-right)

Shortcuts:
- Ctrl+C (copy)
- Ctrl+V (paste)
- Ctrl+B (bold)
- Ctrl+Minus (-) (delete row or column)
- Ctrl+Shift+Plus (+) (insert row or column)
- Ctrl+Shift+Arrow Key (highlight block of data)
- Alt+W+F+F (freeze panes)
- F2 (edit cell contents)
- Ctrl-D (fill formulas down)

Import data plus CONCATENATE, UPPER, LOWER, TRIM, LEFT, RIGHT, MID, LEN & SEARCH
16:59
+
Looking up author names for each course
3 Lectures 15:44

In this chapter we cover:

Function:
- Vlookup

Operation:
- Drag and copy formulas

Shortcuts:
- Ctrl+PageDown ((move to tab to-the-right)
- Ctrl+Shift+Arrow Key (highlight block of data)
- F4 (locks cell references inside a formula)

Concept:
- Remember to color code your cells

Use VLOOKUP and learn how to lock cell references
03:42

In this chapter we cover:

Functions:
- Index
- Match
- Index-match (in a column)

Operation:
- Drag and copy formulas

Shortcuts:
- Ctrl+PageUp (move to tab to-the-left)
- Ctrl+PageDown ((move to tab to-the-right)
- Ctrl+Shift+Arrow Key (highlight block of data)
- F4 (locks cell references inside a formula)

Concept:
- Remember to color code your cells

Preview 04:18

In this chapter we cover:

Function:
- Index-match (in a matrix)

Shortcuts:
- Ctrl+PageUp (move to tab to-the-left)
- Ctrl+PageDown ((move to tab to-the-right)
- Ctrl+Shift+Arrow Key (highlight block of data)
- F2 (edit cell contents)
- F4 (locks cell references inside a formula)
- Ctrl+C (copy)
- Ctrl+V (paste)
- Ctrl+Shift+Plus(+) (insert rows)

Concepts:
- Locking only the row (or only the column)
- Why index-match is better than vlookup
- Remember to color code your cells

Use INDEX and MATCH on a matrix
07:44
+
Determining market size by category
2 Lectures 19:33

In this chapter we cover:

Operations:
- Hide
- Group
- Autofilter

Functions:
- Countif
- Countifs
- Sumif
- Sumifs

Shortcuts:
- Shift+Alt+Arrow Key Right (group)
- Alt+H+K (comma format for numbers)
- Ctrl+Shift+Arrow Key (highlight block of data)

Concept:
- Remember to color code your cells

Use COUNTIF(S) and SUMIF(S) plus learn to group and hide
09:17

In this chapter we cover:

Operations:
- Create pivot table
- Make changes to pivot table
- Sort pivot table
- Turn off GetPivotData
- Freeze panes
- Arithmetic (+, -, ×, ÷) within cell formulas

Shortcuts:
- Ctrl+Shift+Arrow Key (highlight block of data)
- Alt+H+K (comma format for numbers)
- Alt+H+P (percent format for numbers)
- Alt+W+F+F (freeze panes)
- Ctrl+B (bold)
- Ctrl-D (fill formulas down)

Concept:
- Remember to color code your cells

Use Pivot Tables to determine market size by category
10:16
+
Are top categories crowded? (advanced)
1 Lecture 18:58

In this chapter we cover:

Functions:
- Rank
- Offset
- Iferror
- Index and Match
- Sum

Operations:
- Create pivot table by copying an existing one
- Center across selection
- Editing formulas by dragging the cell reference

Shortcuts:
- Shift+Arrow Key (highlight cell)
- Ctrl+Shift+Arrow Key (highlight block of data)
- Alt+W+F+F (toggle freeze panes on/off)
- Ctrl+C (copy)
- Ctrl+V (paste)
- Alt+E+S (paste special)
- F4 (locks cell references inside a formula)
- Ctrl-D (fill formulas down)
- Ctrl-R (fill formulas right)
- Ctrl+B (bold)
- Alt+H+A+C (align center)
- Ctrl+1 (opens Format Cells dialogue box)
- Alt+H+K (comma format for numbers)
- Alt+H+P (percent format for numbers)
- Alt+H+W (wrap text)

Concepts:
- Never merge cells
- Color code your cells

Note: this chapter contains advanced material - it's normal to watch it more than once

Use RANK, IFERROR and OFFSET to identify crowded categories
18:58
+
What do future customers want?
1 Lecture 19:22

Download the "Survey data.xlsx" file to follow along in this section.

In this chapter we cover:

Functions:
- Countif
- Index and Match
- Average, Median, Min, Max, Quartile
- If
- Isnumber, Istext, Isblank, Iseven, Isodd

Operations:
- Move or copy tabs
- Autofilters
- Conditional formatting

Shortcuts:
- Shift+Arrow Key (highlight cell)
- Ctrl+Shift+Arrow Key (highlight block of data)
- Ctrl+Shift+Plus (+) (insert row or column)
- Ctrl+C (copy)
- Ctrl+V (paste)
- Alt+A+T (toggle autofilters on/off)
- Alt+H+W (wrap text)
- Alt+H+A+C (align center)
- Alt+H+A+L (align left)
- Ctrl+D (fill formula down)
- Ctrl+R (fill formula right)
- Ctrl+B (bold)
- Alt+H+K (comma format for numbers)
- Alt+H+P (percent format for numbers)
- F4 (locks cell references inside a formula)
- F2 (edit cell contents)
- Ctrl+PageUp (move to tab to-the-left)
- Alt+W+F+F (toggle freeze panes on/off)

Concepts:
- Make sense of data before using it
- Place data summaries at top of tab, not below raw data
- Color code your cells

Use MAX, MIN, AVERAGE, QUARTILE, IF, ISNUMBER & conditional formatting
19:22
+
How much will it cost?
1 Lecture 05:20

Download the "Cost data.xlsx" file to follow along.

In this chapter we cover:

Function:
- Sumproduct

Operations:
- Move or copy tabs
- Paste special - transpose
- Format painter

Shortcuts:
- Ctrl+PageUp (move to tab to-the-left)
- Ctrl+PageDown (move to tab to-the-right)
- Ctrl+Shift+Plus (+) (insert row or column)
- Ctrl+Minus (-) (delete rows)
- Ctrl+C (copy)
- Ctrl+V (paste)
- Ctrl+X (cut)
- Ctrl+1 (opens Format Cells dialogue box)
- Alt+E+S (paste special)

Concept:
- Color code your cells

Make tables attractive and use SUMPRODUCT to determine cost
05:20
+
Conclusion
1 Lecture 07:09

In this chapter we cover:

Operations:
- Data table

Shortcuts:
- Shift+Alt+Arrow Key Right (group)
- Ctrl+C (copy)
- Ctrl+V (paste)
- Ctrl+Shift+Arrow Key (highlight block of data)
- Alt+H+K (comma format for numbers)
- Alt+H+P (percent format for numbers)
- Ctrl-B (bold)
- Alt+H+A+R (align right)

Concept:
- Color code your cells

Congratulations on finishing the course! I hope you enjoyed it and found it useful. If you'd like to further explore the model we built in this course, you can download the "Market analysis-v8.xlsx" file. Happy modeling!

Update December 2013: I added "Summary Sheets.pdf" - it contains one-page summaries of the keyboard shortcuts (for both PC and Mac) and of the topics we covered. You can print and refer to these as needed. Thanks to those of you who made this suggestion - feel free to ping me with other ideas to add to the course!

Use scenario tables to reach a conclusion
07:09
+
Practice
1 Lecture 00:00

If you are looking for an exercise to practice with, this is it! Details are in the 1-page pdf, and 4 data files are available for you to download and work with.

Description of the exercise
1 page
About the Instructor
Kieran Luke
4.4 Average rating
695 Reviews
3,145 Students
2 Courses
GM, General Assembly

Kieran was a Principal at The Boston Consulting Group, where he served clients, led undergraduate hiring and trained new consultants on topics including spreadsheet modeling, slide writing and presenting. He currently leads Credentials at General Assembly.

Originally from Australia, Kieran lives in New York and enjoys tennis, travel and peaty scotch.