Excel before your internship starts
Congrats on the internship. Most teams will expect you to know your way around Excel from day one, and there usually isn't much time for anyone to show you.
I've picked out the lessons I'd do first if I were starting an internship next month, in roughly the order the work tends to come up. Each one is a short task on a spreadsheet like one you'd get at work, and it checks your formula as you go. They take about ten minutes each, so a session is around half an hour.
Session 1: Get around a spreadsheet
A lot of the first week is working in someone else's file, so start with totals, copying formulas and getting around without the mouse.
- LESSON 1 · OPENYour first formulas: SUM, AVERAGE, MAXTotals and averages on a month of costs.OPEN
- LESSON 4 · FREE ACCOUNTShortcuts: move, select and sum without the mouseMoving around and selecting with the keyboard instead of the mouse.FREE
- LESSON 2 · FREE ACCOUNTCopying formulas and locking references with $Copying one formula down a column without the references moving.FREE
Session 2: Percentages and margins
You'll be working out growth on last year and margins all the time, in pretty much any team.
- LESSON 3 · FREE ACCOUNTPercentages and GST: from till takings to revenueTaking GST out of takings and working out each store's share.FREE
- LESSON 7 · PASSPercentage change and marginsGrowth on last year, gross margin and the change in margin.PASS
- LESSON 8 · PASSRounding properlyRounding so a table still adds up to its total.PASS
Session 3: Summarise a big table
A typical early job is getting a big export and being asked something like how much each region spent last quarter.
- LESSON 16 · PASSIF, COUNTIF and SUMIF: screening acquisition targetsCounting and totalling only the rows that meet a test.PASS
- LESSON 12 · PASSCOUNTIFS and SUMIFS: several conditions and date rangesTotals by region and date range. You'll use this one a lot.PASS
- LESSON 9 · PASSMIN, MAX and simple statsQuick checks like how many stores there are and which is biggest.PASS
Session 4: Pull numbers from another table
You'll often have two lists, say store numbers in one and store names in another, and need to bring them together. That's what lookups are for.
- LESSON 21 · PASSVLOOKUP exact match: pulling a company's numbers by tickerMost older files you'll inherit use it.PASS
- LESSON 27 · PASSXLOOKUP: lookups with if-not-found and search from the bottomThe newer way to do the same thing, and easier to get right.PASS
- LESSON 26 · PASSINDEX and MATCH: lookups to the left and two-way lookupsLookups VLOOKUP can't do, like looking to the left.PASS
Session 5: Check your work
Before you send anything to your manager, it's worth checking it. These help you spot errors and gaps.
- LESSON 20 · PASSIFNA, ISNUMBER, ISBLANK and IFERROR: handling errors in a comps tableDealing with #N/A and divide-by-zero errors.PASS
- LESSON 18 · PASSTRUE, FALSE, AND, OR and NOT: selecting journals to testFlagging rows to look at, like journals posted on a weekend.PASS
- LESSON 10 · PASSCounting things: COUNT, COUNTA, COUNTBLANKFinding the gaps, like debtors who haven't replied yet.PASS
Depending on your team
- Audit and assurance: INDEX: pulling audit samples by row number and Operators and order of operations
- FP&A and corporate finance: Mixed references: one formula for a whole sensitivity table and SUMPRODUCT: weighted averages, like a company's cost of debt
- Banking and corporate development: NPV: valuing a project, and the year 0 trap in Excel's NPV and IRR and XIRR: the return on an investment, and why real cash flows need dates
It's also worth reading the set-up tips (a few settings that make the shortcuts work properly) and the shortcut list for Windows and Mac before you start.
Questions
How much Excel do I need for an internship?
Not as much as you might think. Nobody expects an intern to build a model. You'll mostly be totalling and summarising tables, pulling numbers across with lookups and checking your work, which is what the sessions above cover.
I use a Mac at uni but the firm gives me a Windows laptop. Does that matter?
The formulas are the same but a lot of the shortcuts are different. If you switch to Windows in the settings at the top of the page, every lesson shows the Windows keys, so you can get used to them before you start.
My team uses Google Sheets. Is this still useful?
Yes. Most lessons have a Google Sheets version too. Switch to Sheets in the settings at the top of the page.
How long does it take?
About ten minutes a lesson, so half an hour a session. You could get through all five in the week before you start.
Semester pass
Lesson 1 is free to try and lessons 2 to 4 need a free account. The pass unlocks every lesson on this page and the rest, in Excel and Google Sheets. A$49 every six months; cancel any time. Terms and refunds.