Limited-time offer! Save up to 50% Off | Solutions starting at $6 each  

After 30 years, Microsoft Excel remains ubiquitous in business.

Added on - 21 Jan 2022

Trusted by 2+ million users,
1000+ happy students everyday
Showing pages 1 to 8 of 106 pages
After 30 years, Microsoft Excel remains ubiquitous in business. The world’s quarter of a billion knowledge
workers on average spend half an hour in the application every day. But despite this, Excel’s full capabilities
are still poorly understood. Of 100,000 workers we've tested over the past three years, less than half know
what Conditional Formatting - an essential feature - even does.
So what are Excel’s essentials? We reviewed articles written by Excel experts and combined this with aggre-
gated data from thousands of our customers to compile a list of the 100 most important Excel functions,
features, tips, tricks and hacks, ordered by utility. Where are your favourites?
How to use it
How many do you already know? Excel experts should know 80+, proficient users 60+, average users 40+ and
if you know fewer than 40, we’d class you as a beginner. Scan the list for tricks which:
a) you agree will be useful for you
b) you don’t yet know
c) ideally don’t take too long to learn
If you can find a handful that fulfil these criteria, learn them and become a more powerful human!
For each tip, there are
several stats:
Percentage ofExcel With Businessusers who answered the filter
questions of the Excel course correctly.
Foundation
Presentation
Orientation & Efficiency
Admin
Data Handling
Data Analysis
Measure of complexity, sophistication and conceptual trickiness. Out of 5.
How useful it is, as judged by our internal experts, dozens of experts worldwide
and the usage data of our several hundred thousand customers. Out of 100.
Which of these Excel
categories this tip
belongs to:
Time in minutes the average learner takes to get from no knowledge to
proficient.
Questions:
1Conditional Formatting
2PivotTables
3Paste Special
4Add Multiple Rows
5Absolute References ($)
6Print Optimisation
7Extend Formula Across/Down
8Flash Fill
9INDEX-MATCH
10Filters
11SUM
12Ctrl Z
13Format Cells
14VLOOKUP
15Ctrl C
16Ctrl V
17Basic Arithmetic
18COUNT and COUNTA
19Remove Duplicates
20COUNTIF
21Options Advanced
22Charts
23Freeze Panes
24SUMIF
25Protect Sheet
26F4
27Sort
28Save As (F12)
29Move or Copy Column/Row
30Ctrl (Arrows, PG Up, Etc)
31IF
32Linking Cells (EG '=A1=B1')
33Wrap Text
34IF and ISERROR
35Data Validation
36Use of '(Apostrophe)
37Resize Columns/Rows
38F2
39Alt Enter
40Number Formats
41Layout, Design & Formatting
42Redo (Ctrl Y)
43Cumulative Sum
44Find and Replace
45& and CONCATENATE
46Extend Selection
47Slicers
48Ctrl Tab
49MAX, MIN
50Comments
51Forecast
52Insert Symbols
53FIND
54Customise Status Bar
55LEN (and LEFT/MID/RIGHT)
56Average
57Ctrl Shift A
58Goal Seek/What-If
59Select All
60Precedents & Dependents
61UPPER, PROPER, Etc
62Power Pivots
63Templates
64Quick Access
65Ctrl P
66Group/Ungroup
67Customising the Ribbon
68Ctrl S
69Sparkline
70Copy-Drag Worksheet
71Macros & VBA
72AND
73Rotate Text
74Insert (Ctrl Shift +)
75Autocorrect
76Right-Click on Cell
77AutoSum (Alt =)
78Text to Columns
79Ctrl Space
80RANDBETWEEN
81Tables
82Named Ranges/Name Manager
83Double Click to Rename Sheet
84Date and Time Functions
85Calculate Discounts/Growth
86Double Click Format Printer
87Timeline
88New Workbook (Shift-F11)
89Ctrl 5 (Strikethrough)
90INDIRECT
91Italicise and Embolden
92Rounding
93Waterfall Chart
943D Sum
95Get External Data (From Web)
96Show Formulas (CTRL ¬)
97Ctrl U [Underline]
98Tell Me What You Want to Do
99Shift Space
100Delete Row (CTRL 9)
The top 100 tips
Click on a tip to jump straight to it!
Making sense of our data-rich, noisy world is hard but vital. Used well, Conditional Formatting brings out
the patterns of the universe, as captured by your spreadsheet. That's why Excel experts and Excel users
alike vote this the #1 most important feature. This can be sophisticated. But even the simplest colour
changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three
clicks can reveal the top 10% performing salespeople and tee up an important business conversation.
A cell changes colour, depending on the number entered into it. What's going on?
A. Conditional formatting - user-defined rules are changing the colour
B. Error checking - Excel is automatically spotting problematic data entries
C. Data validation - a way of controlling user input
01. Conditional Formatting
1003180 minPresentation
Next
At 4 hours to get to proficiency, you may be put off learning PivotTables but don't be. Use them to sort,
count, total or average data stored in one large spreadsheet and display them in a new table, cut howev-
er you want. That's the key thing here. If you want to look only at sales figures for certain countries,
product lines or marketing channels, it's trivial. Warning: make sure your data is clean first!
Which best describes the function of PivotTables?
A. They are a way of allowing users to enter data into Excel
B. They are a set of formatting templates for data
C. They allow tables of data to be summarised in a flexible way
02. PivotTables
94.83240 minData Analysis
BackNextIndex
BackNextIndex
Grabbing (ie Copying) some data from one cell and pasting it into another cell is oneof the most
common activities in Excel. But there's a lot you might copy (formatting, value, formula, comments, etc)
and sometimes you won't want to copy all of it. The most common example of this is where you want to
lose the formatting - the place this data is going is your own spreadsheet with your own styling. It's
annoying and ugly to plonk in formatting from elsewhere. So just copy the values and all you'll get is the
text, number, whatever the value is. The shortcut after copying the cell (Ctrl C) is Alt E S V - easier to do
than it sounds.
The other big one is Transpose. This flips rows and columns around in seconds. Shortcut Alt E S E.
03. Paste Special
87.9110 minOrientation & Efficiency
BackNextIndex
Probably one of the most frequently carried out activities in spreadsheeting. Ctrl Shift + is the shortcut,
but actually it takes longer than just right-clicking on the row numbers on the left of the Excel display.
So Right Click is our recommendation. And if you want to add more than one, select as many rows or
columns as you'd like to add and then Right Click and add.
04. Add Multiple Rows
87.5010 minOrientation & Efficiency
desklib-logo
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document