In a 2018 article, human resources and financial industry firm Robert Half wrote that 63% of financial companies continue to use Excel as their primary tool. Of course, this is not 100% and is actually considered a reduction in usage! But given that software is spreadsheet software and not just software for the financial industry, 63% is still a significant part of the industry and helps demonstrate how important Excel is.
Learning to use Excel shouldn’t be difficult. By doing this step by step, you can move from beginner to expert (or at least closer to this point) – at your own pace.
As a preview of what we’re going to cover in this article, think about worksheets, basic useful functions and formulas, and navigating a worksheet or workbook. Of course, we won’t go over every possible Excel function, but we’ll cover enough to give you an idea of ??how to approach the other functions.
Basic definitions
It will be really helpful if we give some definitions. Chances are, you’ve heard these terms (or already know what they are). But we’ll go over them to be sure, and be ready for the rest of the process of learning how to use Excel.
Workbooks versus worksheets
Excel documents are called workbooks, and when you first create an Excel document (workbook), many (not all) versions of Excel automatically include three tabs, each with its own blank worksheet. If your version of Excel doesn’t do this, don’t worry, we’ll learn how to create them.
| Discover the differences between Google tables and Microsoft Excel workbooks |
Worksheets are the actual parts where you enter data. If it’s easier to visualize it, think of worksheets as tabs. You can add tabs or remove tabs by right-clicking and choosing the delete option. These worksheets are the actual spreadsheets that we work with, and they are located in the workbook file.
Feed
The Ribbon is distributed throughout Excel as a series of shortcuts, but the shortcuts are displayed visually (with textual descriptions). This is useful when you want to get things done on a short notice, and especially when you need help figuring out what you want to do.
The buttons on the ribbon are grouped differently, depending on which section / group you choose from the top menu options (for example, Home, Insert, Data, Browse, etc.), and presented visual parameters belong to these groups.
Excel Shortcuts
Shortcuts help you navigate Excel quickly, so it’s helpful (but not necessary) to learn. Some of them can be found by looking at the shortcuts listed in the menu for older versions of Excel, and then trying them yourself.
Another way to explore Excel shortcuts is to view a list of them on the Excel Developers website. Even though your version of Excel doesn’t display shortcuts, most of them still work.
Formulas and functions
Functions are built-in Excel capabilities that are used in formulas. For example, if you want to insert a formula that sums the numbers in different cells of a spreadsheet, you can use the SUM () function to do so.
Read more about this feature (and other features) later in this article.
Formula Bar
The formula bar is the area that appears below the ribbon. It is used for formulas and data. You enter data into a cell, and it also appears in the formula bar if you hover over that cell.
When we refer to the formula bar, we simply indicate that we should enter the formula at that location, with the appropriate cell selected (which, again, happens automatically if you select a cell and start typing).
Example of creating and formatting a worksheet
There are many things you can do with your Excel sheet. In this article, we will give you some sample steps so you can try them out for yourself.
First workbook
It is helpful to start with an empty book. So, go ahead and select New. It may differ depending on your version of Excel, but is usually found in the files area.
Note. In the image above it says “Open†at the top to illustrate that you can navigate to “New†(the left side indicated by the green arrow) from anywhere. This is a screenshot of a newer version of Excel.
When you click New, you will most likely get some sample templates. The templates themselves may differ depending on the version of Excel, but you should get some choice.
One way to learn how to use Excel is to play around with these templates and see what attracts them. In our article, we start with a blank document and experiment with data, formulas, etc.
So go ahead and select the Blank Document option. The interface will vary from version to version, but should be similar enough to get the point across. We’ll also download another sample Excel spreadsheet later on.
Inserting data
Data entry
There are many different ways to fit data into your spreadsheet (or worksheet). One way is to just enter what you want where you want. Select a specific cell and just start typing.
Another way is to copy the data and then paste it into the spreadsheet. Of course, if you are copying data that is not in tabular format, you might be a little curious where it is in your document. But, fortunately, we can always edit the document, and also copy and paste again if necessary.
You can try the copy / paste method right now by selecting part of this article, copying it and pasting it into a blank table.
After selecting a part of the article and copying it, go to your spreadsheet, click the desired cell where you want to start pasting, and do so. The method shown above uses the context menu and then selects Paste as an icon.
It is possible that you might get an error when using the built-in Excel insert method, even with other built-in Excel methods. Fortunately, the error warning (above) helps point in the right direction for getting the data you copied onto the sheet.
When pasting data, Excel interprets it pretty well. In our example, I copied the first two paragraphs of this section and Excel presented it in two lines. Since there was an actual gap between the paragraphs, Excel reproduced that too (with a blank line). If you copy a spreadsheet, Excel reproduces it even better on the sheet.
Alternatively, you can use the button on the ribbon to insert. For visual people, this is really helpful. This is shown in the image below.
Some versions of Excel (especially older versions) allow you to import data (which works best with similar files or CSV – comma separated values ??- files). Some newer versions of Excel do not have this option, but you can still open another file (the one you want to import), use Select All, and then copy and paste it into your Excel spreadsheet.
When an import is available, it is usually found in the File menu. In the newer version (s) of Excel, you can be redirected to more graphical user interface when you click on “File”. Just click the arrow in the upper left corner to return to the worksheet.
Hyperlink
Creating hyperlinks is pretty straightforward, especially when using the ribbon. You will find the hyperlink button on the Insert menu in newer versions of Excel. It can also be accessed using a keyboard shortcut such as command-K.
Data formatting (example: numbers and dates)
It is sometimes useful to format the data. This is especially true for numbers. Why? Sometimes numbers automatically fall into a common format (sort of by default), which is more like a text format. But often we want our numbers to behave like numbers.
Another example is dates, which we might want to format so that all of our dates look consistent, like 20200101 or 01/01/20, or whatever format we choose for our date format.
You can access the data formatting option in several ways, shown in the images below.
Once you have access to, say, a number format, you have several options. These options appear when using the right-click method. When you use the ribbon, your options are right there on the ribbon. It all depends on what is easier for you.
If you have been using Excel for a while, it may be easier to understand the right-click method with the number format dialog box that appears (shown below). If you are a beginner or more visual, the ribbon method might make more sense (and much faster to use). Both provide options for formatting numbers.
If you enter anything that resembles a date, the newer versions of Excel are good enough to reflect that on the ribbon, as shown in the image below.
On the ribbon, you can choose formats for your date. For example, you can choose a short date or a long date. Try it and see your results.
Formatting a presentation (example: aligning text)
It is also helpful to understand how to align your data, whether you want it all to be aligned to the left or right (or justified to width, etc.). It can also be accessed via the feed.
As you can see from the images above, the text alignment (i.e. right, left, etc.) is found in the second line of the ribbon parameter. You can also choose other alignment options (like top, bottom) on the ribbon.
Also, if you’ve noticed, the alignment of things like numbers may not look right when aligned to the left (where the text looks better), but looks better when aligned to the right. The alignment is very similar to what you see in a text editor.
Columns and Rows
Columns and Rows
It is useful to know how to work with columns and rows, as well as adjust their width and size. Fortunately, once you get the hang of it, it should be fairly easy to do.
There are two parts to adding or removing rows or columns. The first part is the selection process and the second is right-clicking and selecting the insert or delete option.
Remember the data we copied from this article and pasted into the blank Excel sheet in the example above? We probably don’t need it anymore, so this is a great example of the process of deleting rows.
Remember our first step? We need to highlight the lines. Go ahead and click on the row number (to the left of the top left cell) and drag down with your mouse to the bottom row you want to delete. In this case, we select three rows.
Then the second part of our procedure is to click Delete Rows and see how Excel removes those rows.
The process for inserting a row is similar, but you don’t need to select more than one row. Excel will figure out where you click and where you want to insert the row.
To start the process, click on the line number you want to place below the new line. This tells Excel to select the entire row for you. From where you are, Excel will insert a row above it. To do this, right-click and select Insert Rows.
As you can see above, we typed 10 on line 10. Then by selecting 10 (line 10), right-clicking and choosing Insert Rows, 10 moved down one line. As a result, 10 are now on line 11.
This demonstrates how the inserted row was positioned above the selected row. Try it yourself to see how the insert process works.
If you need more than one row, you can do this by selecting more than one row and this will tell Excel how much you want and that amount will be inserted above the selected row number.
The following figures show this in visual format, including how 10 fell three lines, the number of lines inserted.
Inserting and removing columns is basically the same, except that you select from the top (columns) rather than the left (rows).
Filters and Duplicates
When we have a lot of data to work with, it’s helpful to have a couple of tricks up our sleeve to make it easier to work with that data.
For example, suppose you have a set of financial data, but only need to view certain data. One way to do this is to use the Excel “Filter”.
First, let’s find an Excel spreadsheet that contains a lot of data so that we have something to check it on (without having to enter all the data ourselves). You can download just such a sample from Microsoft Keep in mind that this is a direct download link, so the Excel sample file should start downloading as soon as you click on that link.
Now that we have a document, let’s look at the amount of data. Quite a bit, isn’t it? Note: The image above will be slightly different from your sample file, and that’s okay.
Let’s say you only wanted to see data from Germany. Use the Filter option on the Ribbon (under Home). It is combined with the “Sort” option on the right (in newer versions of Excel).
Now tell Excel what parameters you need. In this case, we are looking for data on Germany as the selected country.
You will notice that when you select a filter option, small dropdown arrows appear in the columns. When the arrow is selected, you have several options, including the Text Filters option that we’ll be using. You have the option to sort in ascending or descending order.
It makes sense why Excel merges them on the ribbon, as all of these options appear in the dropdown. We will select Equal to under Text Filters.
After we choose what we want to do (in this case a filter), let’s provide information / criteria. We would like to see all the data from Germany, so this is what we enter in the field. Then click “OK”.
You will notice that now we only see data from Germany. The data is filtered. The rest of the data is still there. It’s just hidden from view. There will come a time when you want to stop the filter and view all the data. Just go back to the dropdown and choose to clear the filter as shown in the image below.
Sometimes, you will have datasets that contain duplicate data. It’s much easier if you only have a single piece of data. For example, why would you need the same financial data record twice (or more) in your Excel worksheet?
Below is an example dataset that contains some duplicate data (highlighted in yellow).
To remove duplicates (or multiple ones, as in this case), start by clicking on one of the rows representing duplicate data (which contains duplicate data). This is shown in the image below.
Now go to the Data tab or section and from there you will see a button on the ribbon that says Remove Duplicates. Click on it.
The first part of this process presents you with a dialog box similar to the one you see in the image below. Don’t let this confuse you. It simply asks you which column to look at when identifying duplicate data.
For example, if you have multiple rows with the same first and last name, but mostly gibberish in other columns (like copy / paste from a website), and you only want unique rows for the first and last name, you should select those columns. so gibberish that may not be duplicated is not taken into account when deleting excess data.
In this case, we left the selection as “all columns†because we were manually duplicating rows, so we knew that in our example all columns were exactly the same. (You can do the same with the Excel sample file and test it.)
After you click “OK” in the above dialog box, you will see the result, in which case three lines were identified as being matched and two of them were deleted.
Now the resulting data (shown below) matches the data we started with before we added and removed duplicates.
You’ve just learned a couple of tricks. This is especially useful when working with large datasets. Try other buttons you see on the ribbon and see what they do. You can also duplicate the Excel sample file if you want to keep the original form. Rename the downloaded file and re-download another copy. Or copy the file to your computer.
What I did was duplicate the tab with all the financial data (after copying to my other example file, the one we started with was empty) and with the duplicate tab I had two versions that I could play with as I wanted You can try this by right-clicking the tab and choosing Duplicate.
Conditional Formatting
Conditional Formatting
This part of the article is included in the section on creating a workbook because of its display benefits. If this seems a little tricky, or you’re looking for functions and formulas, skip this section and come back to it at your leisure.
Conditional formatting is useful when you want to highlight specific data. In this example, we are going to use our example Excel file (with all financial data) and search for “Gross Sales†in excess of $ 25,000.
To do this, we first need to select the group of cells that we want to evaluate. Keep in mind that you don’t want to select the entire column or row. You only need to select the cells that you want to rate. Otherwise, other cells (for example, headers) will be evaluated, and you wonder what Excel does with those headers (for example).
So, the desired cells are highlighted and now we click on the section / group “Home” and then on “Conditional Formatting”.
When we click Conditional Formatting on the ribbon, we have several options. In this case, we want to highlight cells that are larger than $ 25,000, so we make a selection as shown in the image below.
Now we will see a dialog box and we can enter a value into the field. We’re typing 25000. You don’t have to worry about commas or anything like that, and it actually works better if you just enter a raw number.
After we click OK, we will see that the fields are automatically colored according to our selection (on the right) in our Greater Than dialog above. In this case, “Light red fill with dark red text.” We could have chosen a different display option.
This conditional formatting is a great way to immediately see the data that is needed for a project. In this case, we might see “Segments” (as they are mentioned in the Excel sample file) with gross sales exceeding $ 25,000.
Work with formulas and functions
It is very helpful to learn how to use functions in Excel. This is the main component of the formulas. If you want to view the list of functions to get an idea of ??what is available, click the Insert menu / group and then select Function / Functions in the far left corner.
Even though the purpose of this button on the Excel ribbon is to insert an actual function (which can also be done by typing in the formula bar, starting with an equal sign and then starting to type the desired function), we can also use this to see. what is in stock. You can loop through the functions to get an idea of ??what you can use in your formulas.
Of course, it’s also very helpful to just try them out and see what they do. You can select the group you want to view by choosing a category, such as “Frequently Used” for a shorter list of features, but a list that is frequently used (and some of the features for which this article discusses).
We will use some of these functions in the formula examples discussed in this article.
Equal Sign = Sign
The equal sign (=) is very important in Excel. This plays an important role. This is especially true with formulas. Basically, you don’t have an unsigned formula in front of it. And without the formula, it’s just the data (or text) that you entered into that cell.
So just remember that before you ask Excel to calculate or automate anything for you, enter an equal sign (=) in the cell.
If you add the $ sign, it means Excel is not moving the formula. Usually, automatically adjusting formulas (using so-called relative cell references) for changes in a worksheet is a useful thing, but sometimes you may not want to, and with this $ sign you can tell Excel about it. You just insert $ before the letter and cell reference number.
Thus, the relative reference to cell D25 becomes $ D $ 25. If this part is confusing, don’t worry about it. You can go back to it (or play with it with a blank Excel workbook).
The Awesome Ampersand >> &
The ampersand (&) is a fun little formula “tool” that lets you combine cells. For example, suppose you have a column for first names and another column for last names, and you want to create a column for the full name. You can use & for this.
Let’s try this on an Excel worksheet. For this example, let’s take a blank slate so as not to interrupt other projects. Go ahead, enter your first name in A1 and your last name in B1. Now, to combine them, click on cell C1 and enter the following formula: = A1 & ““ & B1 . Please use only part in italics and not any other part (for example, without a period).
What do you see in C1? You should see your full name with a space between your first and last name, as you would normally when entering your full name. The & “” & part of the formula is what produced this space. If you hadn’t added “”, you would have your first and last name without a space in between (try it if you want to see the result).
Another similar formula uses CONCAT, but we’ll learn about that a little later. For now, remember what the ampersand (&) can do for you, as this little tip will come in handy in many situations.
SUM () function
The SUM () function is very convenient and does exactly what it describes. It adds up the numbers that you tell Excel to include and gives you the sum of their values. You can do this in several ways.
We started by typing in some numbers so that we have some data to work with when using the function. We just used 1, 2, 3, 4, 5 and started with A1 and entered each cell down to A5.
Now, to use the SUM () function, start by clicking in the desired cell, in this case we used A6, and typing = SUM (in the formula bar. In this example, stop when you get to the first character “(.” Now click A1 ( topmost cell) and drag over A5 (or the bottommost cell you want to include), then go back to the formula bar and type the closing “)”. Do not include periods or quotes and only parentheses.
Another way to use this feature is to manually enter information in the formula bar. This is especially useful if you have quite a few numbers and scrolling to get them is a little tricky. Start this method in the same way as in the example above, with “= SUM (.
Then enter a reference to the topmost cell. In this case, it will be A1. Include a colon (:) and enter a reference to the bottom-most cell. In this case, it will be A5.
AVERAGE () function
What if you wanted to find out what is the average of a group of numbers? You can easily do this with the AVERAGE () function. In the steps below, you will notice that it is basically the same as the SUM () function above, but with a different function.
With this in mind, we start by selecting the cell we want to use for the result (in this case, A6), and then we start typing with an equal sign (=) and the word AVERAGE. You will notice that when you start typing it, you will be prompted for suggestions and you can click AVERAGE instead of typing the full word if you want.
Make sure there is an opening parenthesis in the formula before adding a range of cells. Otherwise, you will receive an error message.
Now that we have “= AVERAGE (” entered in our cell A6 (or whichever cell you use for the result), we can select the range of cells we want to use, in which case we use A1 to A5.
Keep in mind that you can also enter it manually instead of using the mouse to select a range. If you have a large dataset, typing in a range is probably easier than scrolling through it to select it. But, of course, it’s up to you.
To complete the process, simply enter the closing parenthesis “)” and you get the average of five numbers. As you can see, this process is very similar to the SUM () process and other functions. Once you master one function, the rest will become easier.
COUNTIF () function
Let’s say we wanted to count how many times a certain number occurs in a dataset. First, let’s prepare our file for this function, so we have something to count on. Delete any formula you might have in A6. Now, either copy A1 to A5 and paste starting at A6, or just type the same numbers in the cells going down, starting at A6 and values ??1 and then A7 at 2, etc.
Now, at A11, let’s start our function / formula. In this case, we’re going to type “= COUNTIF (.” Then we’ll select cells A1 through A10.
Make sure you type or select COUNTIF and not one of the other functions like COUNT, otherwise we won’t get the same result.
Before doing the same as with other functions and entering the closing parenthesis “)”, we need to answer the criteria question and enter it after the comma “” and before the parenthesis “)”.
What is determined by “criteria”? This is where we tell Excel that we want it to count (in this case). We entered a comma, then “5”, and then a closing parenthesis to get the number of fives (5) that appear in the list of numbers. The result will be two (2) as there are two cases.
CONCAT or CONCATENATE () function
CONCAT or CONCATENATE () Function
Similar to our example using only the ampersand (&) in our formula, you can combine cells using the CONCAT () function. Go ahead and try using the same example.
Enter your first name in A1 and your last name in B1. Then in C1 enter CONCAT (A1, ““, B1).
You will see that you get the same result as with the ampersand (&). Many people use the ampersand because it is simpler and less cumbersome, but now you can see that you have another option as well.
Note. This function may be CONCANTATIVE in your version of Excel. Microsoft has shortened the function name to just CONCAT, and it tends to be easier to type (and remember) in later versions of software. Fortunately, if you start typing CONCA in the formula bar (after the equal sign), you can see which version your version of Excel is using and you can select it by clicking on it.
Remember that when you start typing it, so that your version of Excel shows the correct function, only enter “CONCA” (or shorter), not “CONCAN” (as the start for CONCANTENATE), otherwise you may not see the Excel clause because this is where the two functions start to differ.
Don’t be surprised if you choose to use the ampersand (&) merge method instead of CONCAT (). This is normal.
If / Then Formulas
If / Then Formulas
Let’s say we want to use the If / Then formula to determine the amount of a discount (like a second discount) in a new column in our example Excel file. In this case, we first start by adding a column, and we add it after column F and before column G (again, in our loaded example file).
We are now entering the formula. In this case, we enter it in F2, and it is “= IF (E2> 25000,” DISCOUNT 2 “). This matches what the formula looks for with the test (E2 is greater than 25k) and then the result if the number in E2 passes this test (“DISCOUNT 2”).
Now copy F2 and paste the cells that follow it in column F.
The formula will automatically adjust for each cell (relative cell reference) with a reference to the corresponding cell. Remember that if you don’t want it to auto-adjust, you can prepend the alpha cell with a $ sign and also a number like A1 is $ A $ 1.
In the image above, you can see that “DISCOUNT 2” is displayed in all cells in column F2. This is because the formula tells him to look at cell E2 (represented by $ E $ 2), not relative cells. So, when the formula is copied to the next cell (like F3), it still looks at cell E2 because of the dollar signs. This way, all cells give the same result because they have the same formula referring to the same cell.
Also, if you want a value to be displayed instead of FALSE, just add a comma followed by the word or number you want to display (the text must be in quotes) at the end of the formula, before the closing parenthesis.
| Pro tip: Use VLOOKUP: Find and find a value in another cell based on some matching text in the same line. |
Manage your Excel projects
Fortunately, with the way Excel documents are formatted, you can do a lot with your Excel workbooks. The ability to have different worksheets (tabs) in your document allows you to have related content in one file. Also, if you feel like you are creating something that might have formulas that work better (or worse), you can copy (right click) your worksheets (tabs) to have different versions of your worksheet.
You can rename your tabs and use date codes to find out which versions are the newest (or oldest). This is just one example of how you can use these tabs to your advantage when managing Excel projects.
Here’s an example of renaming tabs in one of the more recent versions of Excel. You start by clicking on a tab and you get an output similar to the one shown here:
If you do not receive such an answer, that’s okay. You may have an earlier version of Excel, but it is intuitive in the sense that it allows you to rename tabs. You can right-click a tab and be able to “rename” in earlier versions of Excel, or sometimes just type text directly on the tab.
Excel provides you with many options on your journey of learning how to use Excel. Now it’s time to go out and use it! Good luck.
–