Microsoft Excel is a popular spreadsheet and data management tool. It finds constant and daily use in the corporate world. However, to make the most of the computational powers of Excel, you must have a clear understanding of its different functions.
Let’s take a look at the important functions in Excel and some tips and tricks that can help you in making sense of the data.
1. Use Pivot Tables to Arrange the Data
Pivot tables are data organising techniques of Excel that let you analyse, summarise, explore, and present summary data. The tables pivot, i.e. arrange or rearrange data in order to extract important information from a large amount of data. It allows grouping by a specific field and the use of advanced calculations on them.
For instance, if you want to know how many students belong to a certain age group from the collective data of all students, a pivot table can help you segregate that data. To create a pivot table, follow these steps.
1. Go to Insert > Pivot Table.
2. Your pivot table will be automatically populated. You have the option to change the order of the data.
3. In the next step, you will have to choose from the four options available:
- Report Filter: This filter allows you to filter certain rows in your dataset. For instance, if we wanted to create a filter by age range, we can choose to only include students in the specified age range instead of all the students.
- Column Labels: These labels are the headers in the dataset.
- Row Labels: These are the rows in your dataset. Both Column and Row labels can contain data from your columns.
- Value: This section stores numeric values on your Excel sheet. It allows you to look at the data differently. Using the value function, you can find average, sum, count, min, max, count numbers, and do more such operations on your data.
2. Conditional Formatting
Conditional formatting in Excel enables you to highlight important information from the rest of the data. It enables you to change the colour of the cell based on the information it is carrying. For example, if you want to have a look at all the students who have secured more than 80 marks in the exam, you can apply conditional formatting to highlight that. Conditional formatting helps you colour code commonalities so that you get a clearer picture just by looking at the sheet.
In order to apply conditional formatting, select the group of cells you want to be formatted. Click on the Format option and select 'conditional formatting. Create your condition, or you can choose from the pre-set logic. A dialogue box will appear where you have to set additional information about the formatting rule. After you are done, click on ‘OK’.
3. Add More Than One Row or Column
As you create a database, you might constantly need to add more rows and columns to make it more structured. Based on your need, you might even have to insert hundreds of rows and columns in between. Excel allows you to do it all at once, instead of doing it manually.
Select the number of rows you want to add by highlighting the same number of pre-existing rows or columns. Right-click and select "Insert." The same number of rows or columns that you had selected will be added before the selected rows.
4. Remove Repetitive or Duplicate Data
The chances of duplicity get higher in large data sets. If they remain unchecked, these repetitive data can result in misleading conclusions. Therefore they must be removed. However, detecting and deleting each duplicate field manually could be a tedious task.
In order to remove the duplicate data, select the row or column that you want to remove duplicates from. Then select the 'remove duplicates' option from the Data tab. A dialogue box will appear to confirm which data you want to delete. Select 'remove duplicates, and you are done.
5. Use filters to simplify your data
The filter function lets you filter a certain set of data that you need to see at a given time. An Excel sheet can go on to have thousands of rows and columns. You usually don't need to look at all the information at the same time.
Skimming through the entire sheet might get difficult to find specific data. Using filters helps pare down your data to certain rows at any one time. This helps you save a lot of energy and time finding specific fields. Here is how you can add a filter
- Click on a cell
- Select the filter option from the Data tab
- Click on the drop-down arrow next to the cell
- Choose the data you want to be displayed
- Click on apply
6. Split up text information between columns.
Excel makes it very easy for users to split up information between columns without having to do it manually. For example, sometimes you might need to split the name of your client into columns: one for the first name and the second column for the last name, or you might want to separate a company's name from its email.
Select the column you want to split. Select the 'Text to columns’ option from the Data tab. A dialogue box will appear with two options - delimited or fixed width. 'Delimit' option lets you break up the column based on characters such as spaces, tabs, and commas. On the other hand, 'fixed width' allows you to select the exact location where you want to split the information.
7. Transpose columns into rows
While working with a large amount of complex data, you might sometimes need to swap a certain row with a column or vice versa. It takes a lot of time to copy-paste each header; however, you can do it much more quicker with the transpose option.
Select the row or column you want to transpose, right-click on it, and select copy. After that, highlight the cells on your spreadsheet where you want your first column or row to begin. Right-click and select the paste option. A module will appear on your screen - check the transpose box at the bottom. Your row will be transposed to a column or vice-versa.
8. Formulas for Simple Mathematical Calculations.
Excel is designed to help you with simple arithmetic calculations so that you don't have to keep going back and forth to an external calculator. Below are some formulas you can use for simple calculations that involve adding, subtracting, multiplying, or dividing any of your data. Highlight the cell where you want the result to be displayed. Put "=" sign followed by the cell numbers and the arithmetic calculation you want to do. For example, if you want to add the values of E4, E8, and E10, the formula will be "=E4+E8+E10". After entering the formula, hit enter.
To multiply, use the * sign.
To divide, use the / sign.
To add, use the + sign.
To subtract, use the - sign.
In order to prioritize a calculation, you can put the numbers in parentheses as you do in BODMAS.
9. Get the Average of Numbers
You can also get an average of numbers in different cells in Excel. To get an average, select the cell where you want the result to be displayed. Use the formula =AVERAGE(Cell1:Cell2) in the selected cell and hit enter.
We see that Excel can be used to simplify our tasks, especially when working with enormous data. With the tips and tricks shared above, you can make the most out of Excel to make your work easier.
