A Beginner’s Guide to Microsoft Excel [Formulas, Functions, and Tips]
Table of Content
Microsoft Excel is a software that seems too good to be true sometimes. It helps businesses create charts for planning, bring all data in one place, perform arithmetic calculations, keep finances in order, and a lot more. It is the most used software for creating graphs and tables. However, to make the correct use of this tool, you need to know it better.
Even if you know how to add up cells and plug-in numbers in a column, it is still not going to get you far when you need to prepare a report on your metrics. You need to have a bit more excel knowledge than what you learned in grade school.
Whether you want to understand pivot tables, complete your first VLOOKUP, this Excel guide will help you learn everything. It will enable you to use Excel and make more informed decisions.
Microsoft Excel Basics
To get a firm foundation and a good command on the various advanced functionalities of Microsoft Excel, you must begin by learning the basics. Here are a few resources to get you started. These will help even if you’ve never worked on Microsoft Excel before.
- Inserting Rows and Columns
- Autofill Feature
- Format Printer
- Apply Filters
- Sort Feature
- Paste Special
When you are playing around with the data and find that you might need to add more rows and columns, doing it one by one may take a long time. Here is what you can do to add multiple rows or columns in one go.
In a spreadsheet, highlight or select the exact number of existing rows or columns that you want to add. Press the right-click button on your mouse and select ‘Insert.’
Autofill is one of its more popular tricks that you would already know if you have ever used excel. To clear our basics, let us go ahead and check out how it works. This trick allows you to quickly fill adjacent cells with different types of data, including series, values, and formulas.
You can deploy this feature in multiple ways but using the fill handle is the simplest one. To make it work, select one specific cell you want to be the source, find and locate the fill handle at the lower-right corner of the cell, and either double click or drag the fill handle to cover the cells you want to fill.
With a lot of features in Excel, if you don’t format your sheet, it becomes difficult to analyze your data. Excel allows you to make crunching numbers and highlight your data as per your preferences. However, this formatting takes a lot of time and can become tedious sometimes.
Format Printer in excel allows you to copy and paste the formatting from one area of your worksheet to another. You don’t need to waste time at formatting again and again. You can just select the cell you want to replicate, click on the format printer from the toolbar at the top and click on the cells you want to copy formatting to.
While working on huge data files, sometimes you don’t need to look at all the rows at the same time. To filter a certain amount or kind of data, you can apply filters to an excel sheet. Filters help you view to the data that fits in certain criteria.
With the help of filters, you can cut down your data to only look at the required rows at one time. You can apply separate filters in every column and choose the cells you want to look at once.
To apply filters, click on the ‘Data’ tab and select ‘Filters’. To use this feature, click on the arrow next to the column headers and choose the way you want your data to be organized. You can either select the rows you want to be displayed or set them to show data in ascending or descending order.
In some cases, when you have a list of data that has no title or a specific category assigned, for example, the list of your blog posts, or marketing contacts, etc., this feature will help you alphabetize the list.
To use the Sort feature in the list, click on the data in the column you want to sort.
Select “Data” tab in your toolbar and click on the ‘Sort’ option on the left.
If you find ‘A’ at the top of ‘Z,’ it means the list will be sorted in alphabetical order, so you need to click that button once. On the other hand, if you find ‘Z’ at the top of ‘A,’ it means that your list will be sorted in reverse alphabetical order, so you need to click the button twice.
Sometimes, you need to transpose the items in a column of data into a row or vice versa. It takes a lot of time to copy and paste each excel cell information and that too with a strong possibility of making an error.
Instead, you can use the ‘Paste Special’ feature of excel to get this done. Highlight the row or column you need to transpose and then right-click and select ‘Copy.’
Select the cells in the excel sheet where you need your first column or row to begin. Right-click on the selected cells and select ‘Paste Special.’ As soon as the segment appears, select the option to transpose.
Paste Special is one of the most useful functions of Microsoft excel. You can also use this feature to copy formulas, formats, values, or even column widths. It can also help you copy the results of your pivot table where you can format and graph into a chart or table.
Managing data in the excel sheet and finding the information when required is a bit difficult in Microsoft Excel when you will just look for it by scrolling the sheet. These features in Microsoft excel help you manage and analyze your data easily without putting any extra time and effort.
After you have familiarized yourself with the excel interface, let’s dive into core use of the application.
- Arithmetic Formulas
With excel, you can do simple arithmetic such as addition, subtraction, multiplication, and division. Here are the formulas that you can apply in the cells.
- To add the values entered in the cells, you can use the + sign
- To subtract the values entered in the cells, you can use the – sign
- To multiply the values entered in the cells, you can use the * sign
- To divide the values entered in the cells, you can use the / sign
Make sure all formulas in the spreadsheet begin with an equal sign (=) and don’t forget to use parentheses for certain calculations to do them first. For example, check out how =2+2*2 is different from =(2+2)*2.
To perform arithmetic calculations, you can also use these built-in formulas. The major ones include-
- Sum: =SUM(cell range)
- Count: =COUNT(cell range)
- Average: =AVERAGE(cell range)
When you are using these formulas, the series of selected cells gets separated by a comma (,), and you can write cell ranges with a colon (:). For example, you could use =SUM(A4,B4) or =SUM(4,4) or =SUM(A4:B4).
With Conditional formatting, you can change the color of the cells based on the information within the cell. For example, you want to highlight certain numbers that are above average in your spreadsheet.
Highlight the cells you want to apply conditional formatting on and choose “Conditional Formatting” from the Home menu.
Select your logic from the dropdown. The feature also allows you to create your own rule. In the next pop up window, provide more information about your formatting rule. Click on “OK” to see your results automatically appear.
Text to Columns
This feature helps you split out the information that is in one cell into two different cells. For example, if you have someone’s name and email address mentioned in a single cell and you want to split this information into two different cells. Thanks to Microsoft Excel! It is possible.
To get this done, first highlight the column with the information. Select the ‘Data’ tab and click on ‘Text to Columns’ option. In the module that appears, select one of the options – ‘Delimited’ and ‘Fixed Width’.
‘Delimited’ means you want to the information mentioned in the columns based on spaces, commas, and tabs. On the other hand, ‘Fixed Width’ means that you get to select an exact location in each column where you want the information to be split.
Combine Cells Using “&” Sign
We have already learned the feature ‘Text to Column’, which was used to separate the information of one cell into multiple cells. This feature is a sort of the opposite of that. Using this feature, you can combine the information of multiple cells into a single cell.
This feature is very useful in case you are using huge databases where you need to split up data as per your requirement. For example, in a database, you have different columns for first and last names. Or maybe location information is divided into three cells – city, state, and zip code. You can combine this data into one cell by using ‘&’ sign.
To get this done, first put your cursor in the blank cell where you want the full name to appear. Highlight the cell that contains a first name, type in an “&” sign followed by highlighting the cell with the corresponding the last name.
That is not all that you need to do. In case you type in =A2&B2, there will be no space between the first name and last name in the cell. Therefore, to add necessary space, use this function =A2&” “&B2. Adding the quotation marks around the space will put the required space in between the first and last name. To apply this in the multiple rows, simply drag the corner of that first cell downward, all through the cells you want to make these changes to.
With these excel formulas, you can perform arithmetic calculations and apply conditional formatting in the spreadsheet. Let us go further and check out how you can use the Pivot Table in a spreadsheet
Using Pivot tables, you can reorganize your data in an excel sheet. These tables don’t change the data you already have but help you sum up values and relate them to different information in your spreadsheet as per your requirements.
You can create a Pivot Table by clicking on ‘Data’ and then ‘Pivot Table’. You don’t have to enter data into this table, as excel populates it automatically. However, you can always change the order of the data. The table offers four options to choose from.
- Value: The value section helps you look at your data differently. Using this option, you can perform count, sum, max, min, average actions with your data.
- Column Labels: It shows your headers in the sheet.
- Report Filter: You can use this option to look at certain rows in your sheet.
- Row Labels: This option shows your rows in the sheet.
Using Pivot Tables, you can summarize large quantities of data easily and quickly. Even if you have an input table with hundreds or even thousands of rows, these tables help you extract answers to the basic questions about your data with minimal effort.
IF function in excel helps you find out if the condition that you set is true or false for a value provided. If it turns out to be true, you get one result. However, if it is false, you get another result.
The function syntax is – =IF(logical_test, value_if_true, [value_if_false])
This function helps you string multiple IF statements together. It allows you to organize your data, get more specific results, and apply multiple conditions into more manageable chunks.
- COUNTIF Function
Using this function, you can count the number of times a number or word displays in any range of cells.
The formula you will apply is =COUNTIF(range, criteria)
Let us discuss the variables in the formula.
- Range: This variable refers to the range that you want the formula to cover.
- Criteria: This part of the formula helps you select the piece or number of texts you want to count.
With the help of these variables, simply entering the formula in any cell and hitting “Enter” will show the number of times ‘criteria’ appears in the dataset.
The IF function can be a little more complex, let us check out how. Let us take up the blog posting scenario. If you want to find out how many leads your blog has generated – the one you wrote and not the entire team. By using the SUMIF function, you can add up cells in the excel sheet to meet these criteria.
Here’s the formula.
=SUMIF(sum_range, criteria_range1, criteria1, [criteria_range2, criteria 2],…)
Let us understand each part of the formula to understand it better.
Sum_range: The range of cells you’re going to add up to the spreadsheet.
Criteria_range1: This range helps you search the first value when you apply the formula. This value is known as Criteria1, which helps you determine the cells that will be added together in Criteria_range1. Don’t forget to use quotation marks while you’re searching for text. The best part is that you can add as many criteria to the formula as you like.
AND and OR functions are the final members of the IF function family. These functions help you check multiple arguments and provide the returns with the information of whether the argument is TRUE or FALSE. The OR function works when one of the arguments is true and AND works when all of them are true.
Let’s fit in the OR function inside IF function syntax.
=IF(OR(logical1, logical2), value_if_true, [value_if_false])
In simple words, this combined formula helps you find out the value that shows if one of two conditions is true. Using AND/OR functions you can make your formulas as complex or simple as you want once you know the basics of the IF function.
VLOOKUP helps you manage two sets of data in two spreadsheets. With the help of this formula, you can combine these sets of data into a single spreadsheet.
For example, you have a list of your employees’ names and their salaries in one sheet and a list of those same employees’ email addresses and employee IDs in the other and you want all this information to be displayed in one place. VLOOKUP comes into play in this situation.
To use the VLOOKUP formula to combine this data, make sure that you have at least one column that has the same data in both the spreadsheets.
Let us have a look at the formula.
=VLOOKUP(lookup value, table array, column number, [range lookup])
Let us go through the different variables of the formula. The points are applicable when you are combining the data from Sheet 1 and Sheet 2 onto Sheet 1.
- Lookup Value: Lookup value needs to be the same in both spreadsheets. Usually, this is the first value in your first spreadsheet.
- Table Array: Table Array is the range of columns on Sheet 2, the one you are going to pull the data from. It includes the cells of the data identical to the lookup value in Sheet 1 as well as the data you want to copy to Sheet 1.
- Column Number: Column numbers indicate the columns in which you want to copy new data in Sheet 1. You can also copy data from Sheet 3. In that case, you just need to change Sheet 2 to Sheet 3 in the formula.
- Range Lookup: In this section, you write FALSE to make sure that you pull in only exact value matches.
This formula pulls data from the second sheet to the first one, but it only pulls the columns’ information that has identical data. The formula can lead to some boundaries, which is why most of the people using excel prefer the INDEX and MATCH functions instead.
INDEX and MATCH functions help you pull in data from different sheets. Using this function, you can combine data from another dataset to one central location. Let us check out how this function is different from the VLOOKUP feature.
INDEX MATCH decreases the load time for the excel sheet compared to the time that the VLOOKUP function takes.
INDEX MATCH works right-to-left, whereas VLOOKUP works left-to-right. In simple words, if you have the specific column that you need to apply the formula to the right, you will have to rearrange the columns from left to right to be able to apply the formula.
For example, if you want to combine data onto Sheet 1 from Sheet 1 and Sheet 2, but the column values in both the sheets are not the same, in that case, to use a VLOOKUP formula, you would need to switch around the columns. So, INDEX MATCH might be a better option for you.
Let us check out the formula.
=INDEX(table array, MATCH formula)
After applying the Match formula, it becomes-
=INDEX(table array, MATCH (lookup_value, lookup_array))
Here are the variables in detail.
- Table Array: This is the range of columns on Sheet 2 that has the new data you need to combine with the data on Sheet 1.
- Lookup Value: This value is the column in Sheet 1 that has identical values in Sheet 1 and Sheet 2.
- Lookup Array: This value is the column in Sheet 2 that has identical values in Sheet 1 and Sheet 2.
Get the variables straight, apply the information in the INDEX MATCH formula, and combine the information as required.
Microsoft Excel plays a critical role in performing formula-based calculations and managing activities that may need arithmetic and mathematical analysis. Many organizations prefer to keep up to date and systematic records of their programs, products, and activities, and people who are proficient in coming up with Excel macros are considered as assets to the company.
Now that you have become an expert, you should be capable of keeping all your numbers in one place and create one optimized spreadsheet that makes it easy for your organization to navigate all their fundamental information when required.
To develop additional professional skills, you can visit Appy Pie Academy and go through the useful courses that may help you improve your employment prospects and add some extra qualifications to your resume. The best part – they’re all available for free! Good Luck and Get Going!