Falsified Excel Skills on Your CV? Master These 8 Essential Concepts Immediately

Quick Links

• The Excel Interface
• Order of Operations
• Cell References
• Data Types
• Key Data Analysis Formulas
• Filtering and Ordering Ranges and Tables
• Pivot Tables
• Macros and VBAs

Can you explain what a ribbon is? Do you know the three types of cell references? Do you know how to convert a number to a currency? If not, then look no further, as we take you through some key Excel features you need to know to show your expertise.

The Excel Interface

First, let’s take a look at what Excel looks like, what its key elements are called, and what each of those elements do.

1. The ribbon and tabs—Excel has hundreds of different menus and drop-downs, and they’re all accessible through the ribbon, which is the area above where you do your work. The ribbon choices labeled number 1 in the screenshot above—such as Home and Insert—are the tabs. Anytime you want to make things happen, you first need to go to the ribbon and make sure you have opened the correct tab. For example, for formatting, you’ll need the Home tab on the ribbon, and if you want to add a chart, you’ll need the Insert tab.
2. The groups—Once you have opened a tab on the ribbon, you’ll see the different groups in that tab. These groups are designed to clump together similar functions, making it easier for you to find the function that you’re looking for. For example, in the Home tab on the ribbon, you can see the Font group, where you can embolden your text, change the typeface, or make your font larger.
3. The name box—If you select a cell or range of cells, this box shows you the cell reference. In the screenshot above, the name box reads A1, because this is the cell we have selected in this worksheet. The name box is useful for jumping to a specific cell or range of cells you have named , especially handy if your workbook has lots of tabs.
4. The formula bar—When you type a formula into a cell, the formula bar is where you’ll see what you have typed and, if needed, edit your formula. Essentially, the cells show you the final product, and the formula bar shows you the formula you have used to create it.
5. Columns and rows—The columns (A, B, C, and so on) run vertically in your sheet, while the rows (1, 2, 3, etc.) go across.

Order of Operations

Remember your math lessons in school when you were told that you had to follow a certain order to make sure your equations worked correctly? Well, because Excel doubles up as an advanced calculator, it also has a particular order that it follows when dealing with your formulas, and it’s important to know what this order is so that you know what to expect when performing calculations.

At its most basic level, Excel follows the PEMDAS order—parentheses, then exponents (also known as indices), then multiplication and division, and finally, addition and subtraction.

There are other elements in a formula that Excel has to follow (including cell references, percentages, and logical operators), but knowing PEMDAS will be enough to show you understand how Excel’s calculation process works.

Cell References

A cell reference is the name of a cell or range of cells in your Excel workbook, and you can use these in formulas. For example, if you click cell A1 and type

=SUM(A2+A3)

into the formula bar, you’re telling Excel to reference cells A2 and A3, and add their values together.

There are three types of cell references in Excel—relative, absolute, and mixed .

A relative reference (the default reference type in Excel), refers to the relative position of the cell. If you type a formula in cell A1 that involves A2, you’re referencing the cell that is one below where you’re typing. If you were to copy the formula to another cell, the reference would automatically perform the same relative action as in its previous location.

An absolute reference does not change if you copy the formula into another cell. For example, if you type a formula that references cell A1 using an absolute reference, it will always reference cell A1, regardless of where you copy that formula to. To create an absolute reference, we place the dollar ($) symbol in front of the column and row reference: $A$1 (or press F4 after having typed the reference).

A mixed reference combines relative and absolute references at the same time. For example, $A1 is a mixed reference that tells Excel to continually refer to column A, but switch the row number relative to where the formula is located.

Data Types

There are many different types of data that can be entered into an Excel cell, and the importance of having the right data type is that it helps Excel to perform the correct operation according to the type of data. Before you enter any data into a cell, the data type is set to General by default—this is Excel’s way of telling you it’s waiting for something to be entered into the cell so that it can then read what you enter and change the data type accordingly.

The data type can be seen in the Number group of the Home tab on the ribbon, and you can click the drop-down arrow to see the different data types available.

If you were to type $12 into a cell, Excel would see the dollar symbol and automatically change the data type to Currency. Likewise, if you typed Apr 14, Excel would read this as a date. If Excel doesn’t identify the correct data type, you can change it manually using this drop-down list.

Key Data Analysis Formulas

On top of the most basic formulas, such as SUM and AVERAGE , some of Excel’s formulas are particularly handy for data analysis. Here are some that you might need to know if asked about important Excel formulas:

• IF—The IF function returns different values depending on whether a condition is true or false.
• COUNTand COUNTIF—The COUNT lets you count the number of cells in a range that contain numbers, and COUNTIF counts all the cells in a range that match a condition or multiple conditions.
• VLOOKUP—This function searches a table by row to extract certain information.
• CONCATENATE—The CONCATENATE function lets you join two or more strings of data into one place, useful for keeping your key information in once place.

Knowing the many useful data-entry functions is essential if you want to show off your Excel knowledge.

Filtering and Ordering Ranges and Tables

Knowing how to handle data in Excel is crucial for anyone looking to manage the spreadsheet’s numbers, and filtering and sorting the data is one way to do this. There are three ways to filter and order data in Excel—use the Filter icon, format a table, or right-click the data.

If you have an unformatted data set, you can add a filter and sort function by selecting the header row before clicking the Filter icon in the Data tab on the ribbon.

This will create a filter button at the top of each column in your data, which you can click to filter or sort your data.

Another way to filter and sort your data is by formatting your data into a table. Select all your data (including the header row), and click “Format As Table” in the Home tab on the ribbon.

Then, choose an appropriate table style, and make sure you check the “My Table Has Headers” box in the dialog box that appears. When you click “OK,” your data will be formatted in a table with a filter button running across the header row of your table.

Finally, you can sort and filter a specific series of data by selecting and right-clicking your data. Then, choose between the Filter and Sort options in the menu that appears.

Pivot Tables

Even if you haven’t used one in the past, anyone with Excel expertise will know the huge benefits of a pivot table . A pivot table is a tool that lets you quickly convert large amounts of data into a readable and useful table. You can use a pivot table to display specific criteria in a table, control your charts and graphs, and analyze numerical data in detail.

The pivot table option is accessed through the Insert Tab on the ribbon.

Once created, a sidebar appears to the right of your screen, where you can manipulate and choose the data you want to analyze in your table.

Macros and VBAs

Macros and VBAs are ways to automate data in Excel, useful for data managers who want to save time when working with large volumes of data. They also help you to avoid errors that might have occurred had you completed the tasks manually.

As with pivot tables, knowing what they do is the first step to demonstrating Excel expertise—and you’re even more likely to impress if you can demonstrate you know how to use them . Macros and VBAs are closely related:

• Macros—These are fantastic lines of code for automating actions you do repeatedly in Excel, saving you have to go through a series of clicks every time you want a certain action to occur. For example, if you want to turn a certain cell red and change the text color to white, you can create a button within your spreadsheet that lets you do this, especially useful if you have to go through this process repeatedly.
• VBA—Short for Visual Basic for Applications, VBA is the scripting language used to create macros.

If this is sounds a little too technical for you, the key thing to note is that macros and VBAs are useful tools in Excel to create automation.

As well as the tips listed in this article, you might also find knowing how to create a form , a checklist , or a dynamic chart helpful when convincing others of your above-average Excel knowledge.

Also read: