Excel: Basic Skills You Should Have

Man Using ExcelYou’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:

Excel Basics IFSTATEMENT
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:

Excel Basics SUMIF

  • 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:

Excel Basics COUNTIF

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:

Excel Basics COUNTIF2

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:

Excel Basics AVERAGEIF

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 PPT for Trainers Advanced PowerPoint for Trainers

Itching for more? See what you’re missing in PowerPoint with our on-demand webinar, Advanced PowerPoint for Trainers.


Let us show you how we can improve your company’s efficiency and expenditures.

We are committed to innovation, quality, and customer service. Speak to one of our specialists to learn how Mimeo can improve ROI on your print expenditures:

Mimeo Photos Customer SupportClick Here