After 30 years, Microsoft Excel remains ubiquitous in business.
106 Pages10270 Words231 Views
Added on 2022-01-21
After 30 years, Microsoft Excel remains ubiquitous in business.
Added on 2022-01-21
BookmarkShareRelated Documents
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
End of preview
Want to access all the pages? Upload your documents or become a member.