Excel software is very useful, but learning it requires training. If you use the techniques and tips in this article, you can do your job faster and without stress.
In this article you can learn how to write Excel formulas, calculations, filters, data manipulation, workflow efficiency and more. Here we will explain 12 techniques and features of Excel.
Before you start, download the free Excel file worksheet . This file consists of several sheets, each sheet to practice one of the twelve Excel techniques. You can watch the tutorial video above about screencast or continue reading this article.
1- Basics of formula writing
Each formula must start with an equal sign. If you do not use an equal sign, Excel will not calculate and will only display what you typed. In fact, this equal sign tells Excel, “Hello, calculate this.” Think about the result of your formula if you used pencil and paper.
Be sure to write the formula in the cell you want the answer to appear. To do this exercise, refer to the “simple” tab in the free Excel file that you downloaded.
Example: Add the values B2, B3 and B4 and put the total value in B6.
In B6, enter the following formula:
= B2 + B3 + B4
Then press Enter to see the result. If you do not press Enter (or click the small tick just above column A), nothing will happen.
Subtraction, multiplication, division and power work the same way. You can use their usual symbol or see the chart below:
|the operation||A symbol|
|Power||مثال (Example: B5 ^ 2 is equal to B5 to the power of 2)|
|Larger equals||= <|
2. For more complex calculations, use functions
If you want to do calculations that go beyond a simple formula, use a function in your formula. Excel has about 400 functions, so you can find the right option for you. There are functions for business, Ama, finance, date and time, text, document information and more.
The structure of all functions is as follows:
= Function name (……)
If the function is at the beginning of the formula, it should start with an equal sign, then the function name, and finally a set of parentheses. There is usually something in parentheses, but not always.
Example: Find the number and average of the number column. To do this exercise, refer to the “functions” tab in the free Excel file.
In B10, enter the following function:
= SUM (B2: B8)
In B11, enter the following function:
= AVERAGE (B2: B8)
Press Enter to see the result.
3- Use Autofill for fast and smart copy and paste
Suppose you write a formula at the top of a column and you want to use that formula at the bottom of a column (or you have a formula at the beginning of a row and you want to use that formula across the row). Instead of manual copy and paste, you can use the AutoFill feature to speed up your work and adjust the formula automatically. You can also use AutoFill to automatically enter months or days of the week.
To use AutoFill, find the small dot in the lower right corner of the current cell. (If you have selected several cells, you must use their common point.) When you hover the mouse pointer over the point, the cursor becomes an intersection. Drag the intersection to the bottom of the column or along the row.
Example: In D2, do a simple multiplication operation, then AutoFill up to D8. To do this exercise, refer to the “autofill” tab in the free Excel file.
Write the first formula as usual.
Enter the formula. Click D2 again if necessary. Rotate the mouse pointer over the heavy point in the lower right corner of the cell to make it a cross.
Drag the cross to the bottom of column D, or just double-click. The formulas will be in the column and the results will be in the column below.
4- Filtering and sorting data
You can use Excel as a small database, use it to sort data rows alphabetically or by number, or filter the data you want. Excel works best if the columns have different titles such as first name, last name, etc.
The most important thing to remember is that you should not select a column before sorting it. Otherwise that column will be sorted separately from the other data, and this is probably not what you want. When you click on a column, Excel knows exactly what you are doing.
When it comes to filtering, Excel offers good automation tools.
Example: Sort a list by ascending, city, or other column in ascending or descending order. To do this exercise, refer to the “filter and sort” tab in the free Excel file.
Click a cell in the last name column. On the Home tab, click Sort and Filter, then select A to Z or Z to A. Do the same on the other columns.
You may have noticed that you can do this from the same filtering drop-down menu.
Example: Now filter the list to show only the addresses of the states of Alaska and California.
Click on a point in the data field and select Filter from the Sort and Filter menu.
As soon as this is done, slider arrows will appear on the column headers. now:
- Click the slider in the State column.
- Then click on the Select All box to clear all state boxes.
- Select AK and CA again, and click OK.
Currently this list shows only the rows for those two states. If you want, you can sort the states by A to Z so that all the rows in Alaska are on top of the rows in California (as you can see in the image below).
To clear the filters, click the drop-down arrow in the State column again and select Clear Filter.
You can delete the sliding arrows the same way you created them: click on the Sort and Filter menu and select the filter option.
5- Delete duplicate rows
If you have a lot of data, especially if you entered it from somewhere like a web form, you may have duplicate rows. Excel does a good job of eliminating duplicates and lets you decide which rows you think are duplicates (specify how similar duplicate rows are)
Example: Use address information to find duplicate rows. To do this exercise, refer to the “remove duplicates” tab in the free Excel file.
When we move this file up and down, we come across three identical rows with the name Samantha Carter: The data in all three rows are the same, which means that this person is listed three times. There are also two rows named Sharon Valerie, but their information is different, which means that the two people are nominally matched and this data does not belong to one person.
Click on the data section. Then on the Data tab, click Remove Duplicates.
Check all the checkboxes. In this way, Excel considers rows if their columns are the same (duplicate).
Click OK. As expected, two duplicates will be removed. If you scroll down, you will see that two copies of Samantha Carter’s three files have been deleted and two Sharon Valerie files remain.
6- Dividing the data of one column into several columns
If you have a column of data that you want to split into several columns, you should use the Text to Columns command. Just keep in mind that this command is not complete because it cannot read your mind, so you may have to manually clean it.
Example: Divide a column that contains full names into separate columns for first and last names. To do this exercise, refer to the “splitting” tab in the free Excel file.
Here our data is similar to the previous examples. Note that three people in the list have three names, so we will need two empty columns.
First insert the two empty columns to the right of column A:
Place the mouse pointer on the B-column tab to turn it into a down arrow. Drag it to the right so that columns B and C are selected.
Right-click and select Insert from the list that appears. Since you selected two columns, you will now have two empty columns.
Select all the names in column A (Quick way: Click on the first item in A3, then press Ctrl + Shift + Down arrow. And go back up.)
On the Data tab, select Text to Columns. Select Delimited, then click Next.
Select Space as the only delimiter. By default, a selector will be selected with nothing to do with it. Scroll to the bottom of the list to see the names that will appear in the three columns.
Click Next, then click Finish. Column A now shows only the names, column B contains the last names and column C contains the three cluttered names.
You can edit these three names manually, then delete column C which should be blank.
7- Converting several columns into a single column
Excel also works well by merging multiple columns. There are several ways to do this, here I will show you the easiest way to use a formula. To do this exercise, refer to the “joining” tab in the free Excel file.
As you did above, insert a new column after column B. Hover over the C-column header to turn it into a down arrow…
… Then select Insert from the list that appears. Name the new column Full Name.
Click on the top of the new column to perform the first formula in cell C3. To better understand this, we will do this with a small error, then fix it.
Because we want to merge the contents of the cell, we do not add numbers, the operator used is an ampersand. Prepare the formula:
= A3 & B3
When you enter it, the first and last names are placed next to each other, without any space between them. So delete it and rewrite the formula with another item:
= A3 & “” & B3
Placing a space inside quotation marks means that the formula must have a space.
Enter it, and Autofill to the bottom.
8- Quick formatting of numbers and worksheets
Here are some quick ways to format numbers (dollars, percentages, etc.) and worksheets (colors, pens).
To do this exercise, refer to the “formatting” tab in the free Excel file.
The numbers in this worksheet must be commas to separate a thousand, but do not require decimals. You may want to use the dollar sign in the first row, Total row and Total column. The last column is formatted as a decimal but must be displayed as a percentage.
Select the top row of numbers from the Total column.
Hold down the Ctrl key and select the entire Total row at the bottom (top and bottom rows should now be selected).
On the ribbon, click the dollar sign button or press Ctrl + Shift + $. This will apply the della, comma and decimal marks. (We will delete the decimal places.)
Now select all the available numbers and click on the Comma Style button or Ctrl + Shift +! Press. This applies commas and decimals.
To delete decimals, select all the numbers on the tab except the Percent column at the bottom and double-click the Decrease Decimal button.
Let’s apply percentages in this section.
Select all numbers below the % of Total column , then click ٪ or press t Ctrl + Shift +.
If you want, click the Increase Decimal button once or twice .
There are three ways to format a sheet:
- Select row and column headers and apply color, font, etc. to them separately.
- Select row and column headers and apply interior styles.
- Format the whole page as a table and let Excel apply the layout.
Use the second and third methods to speed up this process.
Select all column headers and then click Cell Styles on the ribbon. And select one.
Do the same for row headers.
Undo several times.
Now click on Format as Table and select a layout.
In the window that appears, click OK. Note that the columns have sliding arrows, you can filter them with the help of these arrows.
9- Manipulating columns, rows, page breaks and worksheets
Resize rows and columns
If a column is too narrow, it may cut text. And if a line is too short, you may not be able to read it. To do this exercise, refer to the “rows & columns” tab in the free Excel file.
Adjusting the column width and row height is done in one way: Place the mouse pointer on the right or bottom edge of the row or column to turn it into a 2-headed arrow, then drag it to change the row or column to your desired size. Slowly You can also double-click the row or column to measure automatically. (This function works the same way in the detail view in Windows File Explorer.)
Manipulate Page Breaks
There are two ways to create Page Breaks:
- Manually import them from the Page Layout tab
- Using Page Break Preview
To insert a page break manually, select the row or column where you want the page to break, then from the Page Layout tab, click Breaks drop-down and select Insert Page Break.
Use Page Break Preview to set page breaks .
Click the Page Break Preview icon in the lower right corner, then draw lines. The dots are page breaks, and if you enter them, these lines will be solid.
Adjust the work of the leaves
Use the tabs in the lower left corner of the page to insert, delete, move, or rename worksheets.
This book works from just one tab called Sheet1. Formed to change its name:
- Double-click the tab.
- Type a new name (like N. America), then press Enter.
To insert a new blank tab, click the Plus icon next to the tab.
To copy a sheet:
- Swipe the tab to the right until you see a downward triangle next to it.
- Press Ctrl to see a small plus sign in your mouse pointer.
Release the mouse first. You will see a new tab with the same name and a “(2)” after it.
You can drag these tabs anywhere you want. The tabs will be where you see the downward triangle.
10- Visible column heads
When you have a long tab, the top of the column may protrude, but there is a feature to prevent this. When printing a long sheet, the column headings should appear at the top of each page – (please do not copy and paste this).
Visible headers when searching
In this tab, click on cell A3 just below the row of column headers. Then on the View tab, click on Freeze Panes and select Freeze Panes from the drop-down menu.
You can now scroll up and down the tab and see the headers.
You can decompress the pages in the same way: Click on Freeze Panes, then select Unfreeze Panes from the drop-down menu.
However, this has no effect on printing. To print column headers on each page:
- Go to the Page Layout tab and click on Print Titles.
- Click inside the rows to repeat in the Top box.
- In the worksheet, select headers in rows 1 and 2. This will put $ 1: $ 2 in the box.
To make sure it works, go to Print Preview: Press File> Print or Ctrl + P.
Click the tabs at the bottom of the page to be able to scroll up and down the pages and see the headers.
11. IF Function: Make the cell value conditional
Excel has several functions that can decide what values to display. They all depend on the IF function. If you are familiar with it, it will be easier for you to use more complex items.
The IF function has three arguments:
- What is the condition of your test?
- If the condition is true, what should the cell indicate?
- If the condition is incorrect, what should the cell indicate?
When a function has multiple arguments, we separate them with a comma. Therefore, its structure will be as follows:
= IF (condition to test, what to return if true, what to return if false)
To do this exercise, refer to the “functions” tab in the free Excel file.
Example: We have a list of orders placed by individuals in different states. If the order is from New Jersey, we will add 7.% sales tax on it. And if it is from other states, we do not add taxes.
At the top of the Tax column (H3) , enter the following formula:
= IF (C3 = ”nj”, G3 * .07, “”)
- The test condition is: Does the cell in the State column contain the value “nj”? (Note that this is not case sensitive, and should be enclosed in quotation marks.)
- If this condition is true, the cell should have a sales value of * 7% .
- If the condition is incorrect, the cell should have no value (no quotation marks).
Autofill to the bottom. Many of the cells in the tax column will be empty, and those that are not empty will be taxed on sales because that row, as shown below, in the column, belongs to the state of NJ.
12- SUMIF and: AVERAGEIF Add and average depending on the situation
Like the IF function, the SUMIF and AVERAGEIF functions find the sum and average of a wide range of cells in which there is a certain value or condition.
These functions have three arguments:
- To find the condition, in what range should we search?
- What is the condition or amount to be searched?
- In the rows where that value exists, from which range do we get the sum or the mean?
The structure of the two functions is similar – but their names are different. The arguments are the same:
= SUMIF (range to look in, value to pick, range to add)
= AVERAGEIF (range to look in, value to pick, range to add)
To do this exercise, refer to the SUMIF tab in the free Excel file.
Example: Find the total number of California orders, and the average Kona orders.
Do a SUMIF first
At the bottom of the Sale column, in G203, enter this formula to find all California orders:
= sumif (C3: C201, “ca”, G3: G201)
- Look at the bottom of column C.
- In column C, select the rows containing ca.
- In the same row where you find ca, add the value of column G to the whole.
Do an AVERAGEIF
Now use the above technique to find the average Kona total sales.
Go to G204 and enter the following formula:
= average (D3: D201, “cone”, G3: G201)
- Look at the bottom of column D.
- In column D, select the rows containing kona.
- In the same row where you find kona, enter the average value of column G.
The result should look like this:
Use Excel techniques
Learning how to use Excel is very valuable. By using these techniques and tips, you can use its features and save your time. Practice the above lessons, then apply these techniques in your work.