Use Excel Like A Pro. Fast.

Go from spreadsheet rookie to rockstar
4.5 (513 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.
2,874 students enrolled
$50
Take This Course
  • Lectures 15
  • Length 2 hours
  • Skill Level Intermediate Level
  • 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 10/2013 English

Course 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

What are the requirements?

  • None

What am I going to get from this course?

  • 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

What is the target audience?

  • For students going into business careers and professionals / entrepreneurs seeking to improve their excel skills

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: Designing and setting up your model
01:01

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.

04:11

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

07:59

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)

Section 2: Moving fast and looking good
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)

Section 3: Importing data and cleaning text
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)

Section 4: Looking up author names for each course
03:42

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

04:18

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

07:44

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

Section 5: Determining market size by category
09:17

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

10:16

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

Section 6: Are top categories crowded? (advanced)
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

Section 7: What do future customers want?
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

Section 8: How much will it cost?
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

Section 9: Conclusion
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!

Section 10: Practice
1 page

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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Kieran Luke, 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.

Ready to start learning?
Take This Course