Excel Training: Basic Skills You Should Have You’ve endorsed people for it, had training on it: It’s Excel! Learn how Excel is used for more than just classic intern-level data entry. Published on 13 December, 2018 You’ve endorsed former classmates or coworkers for it, recruitment companies test you on it, and it seems more complicated than it has to be. It’s Excel– a program that’s used for more than just classic intern-level data entry. In this post, we’ll cover the basics to give you the tools you need to be able to navigate Excel like the number-crunching champion we know you are. Getting Started There are a plethora of things you can do through Excel, and while that can seem daunting we’ve outlined the very basics. The most basic functions that we’ll cover are Shortcuts, Common Formulas, and Uses. Shortcuts Skipping ones like Ctrl + A / ⌘Cmd + A that are basic for all applications, we’ll be touching on the ones that are unique to Excel itself. The most basic shortcuts are as follows: Windows Users Ctrl + R This moves the content within a cell to the right. Ctrl + 1 Change the format of the cells you selected Ctrl + 9 Hide row(s) Ctrl + 0 Hide column(s) Ctrl + Shift + : Insert the current time Ctrl + ; Insert the current date Ctrl + Shift + % Applies percentage formatting for the selected cell(s) Ctrl + Home Moves your selection to cell A1 Ctrl + End Moves your selection to the last available cell on your current worksheet Ctrl + ↑/↓ Move your selection to the top (↑) or bottom (↓) of the column you’re currently in. Ctrl + ←/→ Move your selection to the leftmost (←) or rightmost (→) cell of your current row. Ctrl + Shift + ↑/↓ Highlight/select all the cells above (↑) or below (↓) your current cell selection. Shift + F11 Create a blank new Worksheet within your current Workbook. F2 Edit the formula for your currently selected cell Alt + = Add the values of all of the cells above your currently selected cell Mac Users ⌘Cmd + R This moves the content within a cell to the right. ⌘Cmd + 1 Change the format of the cells you selected ⌘Cmd + 9 Hide row(s) ⌘Cmd + 0 Hide column(s) ⌘Cmd + Shift + : Insert the current time ⌘Cmd + ; Insert the current date ⌘Cmd + Shift + % Applies percentage formatting for the selected cell(s) ⌘Cmd + Home Moves your selection to cell A1 ⌘Cmd + End Moves your selection to the last available cell on your current worksheet ⌘Cmd + ↑/↓ Move your selection to the top (↑) or bottom (↓) of the column you’re currently in. ⌘Cmd + ←/→ Move your selection to the leftmost (←) or rightmost (→) cell of your current row. ⌘Cmd + Shift + ↑/↓ Highlight/select all the cells above (↑) or below (↓) your current cell selection. Shift + F11 Create a blank new Worksheet within your current Workbook. F2 Edit the formula for your currently selected cell Alt/Option + = Add the values of all of the cells above your currently selected cell While these are the most common, there are many, many more. Most Common Formulas (You Should Know) Shortcuts are great for easily inputting your content, and formulas help you do something with it. There are three basics that you should know, though, of course, there are many, many more: SUM Formula: =SUM(A1,B1,etc.) or =SUM(A1:B1) Pretty self-explanatory, this formula enables you to quickly add many values together. When you use a comma to separate the values, you’ll be adding each chosen cell together. However, when you separate with a colon, you’ll be adding together all of the cells within that selection– not just the ones that you’ve chosen. IF Statements Formula: =IF(cell_value/column/row, [value_if_true], [value_if_false]) IF statements are great for aggregating data based on expectations that you want it to meet. For example, if you’re A/B testing marketing material or tracking for course completion, you can use an IF Statement to easily analyze and discover your findings. For example, let’s say that I want a click-through rate of 0.50% for a couple of different search ads that I created. I’m A/B testing for a variety of components and I want to easily understand the information I’ve collected. Using the IF Statement, my Excel Workbook will look like this: There are different ways to apply to IF Statement formula: SUMIF Formula: =SUMIF(A4:A18,” >NUMBER”,B4:B18) The former cell values reflect the rules that will be applied when determining what is added together. The latter cell values are what is being added together. SUM, as we went over above, means adding values together. When combined with the IF Statement formula you’ll only be adding values together that meet certain criteria. For example, if you’d like to compare the highest and lowest commission amounts you can attain, using a SUMIF is very useful: COUNTIF Formula: =COUNTIF(C2:C11,”XYZ/123”) COUNTIF, like the name suggests, is used to count how many cells fit the conditions that you’ve set. COUNTIF is especially useful when trying to see how many sales over a certain value were made, for example, or even when counting how many votes were cast for something: Let’s say that you’re using this for a vote, but not everyone will write the person’s name in the same way. As long as there’s one constant between them, you’ll be able to use COUNTIF: Additional use case examples can be found here. AVERAGEIF Formula: =AVERAGEIF(E4:E18, “>#/XYZ”) Like the others in the IF Statement “family,” AVERAGEIF takes the average of any numbers that meet the conditions you’ve set. This formula could be particularly useful for trainers. If you’d like to see how the average completion rate for students who have completed more than half of the material, it might look like this: This formula can also be applied if you’re trying to see the average sales of a product, the average number of packages that were delivered, or even average attendance on test days. TRIM Formula: =TRIM(Cell Value) Download a file that is barely legible due to all of the extra spacing? Use TRIM to get rid of extra spaces! Getting Even More Use Case Specific… We’ve touched on some examples for uses within the formulas, but there are, of course, many other use cases for Excel. If you’re a marketer or in accounting, you might want to keep these formulas handy: Marketing Accounting Excel is as versatile as you need it to be. Whether you’re tracking a completion rate, the click-through rate on your ads, or tracking your commission, there’s a formula and/or shortcut for you. Advanced PowerPoint for Trainers Itching for more? See what you’re missing in PowerPoint with our on-demand webinar, Advanced PowerPoint for Trainers. twitter Tweet facebook Share pinterest Pin Next Post Previous Post Mimeo Marketing Team Mimeo is a global online print provider with a mission to give customers back their time. By combining front and back-end technology with a lean production model, Mimeo is the only company in the industry to guarantee your late-night print order will be produced, shipped, and delivered by 8 am the next morning. For more information, visit mimeo.com and see how Mimeo’s solutions can help you save time today.