Simple Excel Tricks EXCEL is a fantastic programme! It allows you to work more effectively on all types of calculations and eliminate laborious manual effort. However, mastering it, like any reasonably complicated technologies, requires a large initial investment. So, to make your life simpler and prevent spending valuable time on geek forums……we’ve compiled a list of the most important tips to help you use Excel even if you’re a newbie. Pivot tables, conditional formatting, shortcuts… Excel will soon no longer be a mystery to you! Discover our top 15 techniques for fast becoming an Excel guru.
1. Make a pivot table
Simple Excel Tricks Pivot tables are used to automatically summarise raw data and restructure it into a new spreadsheet. This saves you from “taking the pain” of sorting them yourself or creating numerous algorithms to get a result. The Pivot Table (TCD) will handle everything for you. Consider a database with several rows and columns including names, first names, and dates of birth. You’re curious about the most popular first names by year. Simply construct a pivot table, and the programme will take care of transcribing the data for you.
To create a pivot table, go to Insert > PivotTable > Select Data Range > PivotTable. Select a PivotTable Location…… And there you have it!
Excel generates a pivot table for you:
This pivot table (DCT) allows you to automatically generate summaries without having to use time-consuming algorithms. For example, if you want to know how many times a name occurs in your source table, the TCD will automatically deliver the answer to you. Simply drag the “Name” icon into the area field > “Columns” and then into the area field > “Values” to do this. The TCD then returns the following result:
You may also make your summary more personal by adding the initial names in line or column. Drag the data icon from “Columns” to “Rows” according to your layout preference:
Finally, by clicking on a value in the “Value” box, you may instruct Excel to do automated computations. For example, you could wish to know the percentage that the names in your table represent. Click Number of Names in ‘Values’ area field, ‘Values Field Settings,’ Show Values, and then percent of Grand Total.
2. Add multiple rows or columns at the same time
Simple Excel Tricks As you get more familiar with Excel, you will notice that you often need to add new rows and columns. And occasionally you’ll need to add hundreds of rows all at once! It takes a long time and effort to add a line one at a time. Fortunately, there is a far simpler and quicker method to do this. Here’s a straightforward way to add numerous rows or columns to a worksheet at once. Choose the same number of existing rows or columns as you wish to add. Then, right-click and choose “Insert.” For example, if you want to add ten more rows to a table, choose ten rows and then click “Insert.”
3. Filter your data
Simple Excel Tricks Do you have a large amount of data on your Excel spreadsheet? Then you will undoubtedly need to filter your data in order to evaluate it more readily. Here’s where filters come in handy. Filters enable you to choose and show your data in order to see just certain rows at the same time. This filter may be applied to any column of data. After that, you may choose which cells to see at the same time. To create a filter, first choose your table headers > then go to HOME > click on “SORT and FILTER” and then on “FILTER.” Small upside-down arrows will now display in your table header cells. Continuing with the surnames, first names, and dates of birth example, if you just want to show a certain name, simply click on the filter in the header and choose the name that interests you.
Pro Tip: If you use the filter to pick a certain sort of data, transfer those numbers to another spreadsheet if you need to study them.
4. Remove Duplicates
Simple Excel Tricks Duplicate material is common in large databases, which might bias your findings. As a result, it is critical to erase them in order to preserve crucial data. But how can you find duplicates when you have an infinite database? I can’t watch line by line… So take advantage of this fantastic technique. To get rid of them, choose the row or column you wish to work on. Then, under the > Data tab, click > Remove Duplicates. A confirmation box will popup asking you to confirm the data you wish to work with. Continue with Current Selection, then Remove Duplicates, and you’re done.
5. Transpose rows to columns and vice versa
Simple Excel Tricks When creating a summary table, the reading orientation may not always be the best. Your data would be better in columns rather than rows, and vice versa. “I have to start all over again!” you think to yourself. No, there is a simple method to execute in this kind of situation, and it is known as “Data Transposition.” Begin by highlighting the column or row you wish to reposition. Right-click on it and choose Copy. Next, on your spreadsheet, choose the column where you want your table to start. Right-click on the cell and choose Paste Special. A module will emerge, with a checkbox option labelled “Transpose” at the bottom. The column or row is then moved in the desired direction.
6. Split text into multiple columns
Simple Excel Tricks This is a useful tip that will make your life simpler! It is common for a cell to have several pieces of text information, such as a name and a first name. Except that for your research, you need to separate the name and first name into two columns rather than one; this is very achievable and extremely simple to accomplish! To begin, choose the column you wish to separate. Then go to the “Data” tab and click “Convert.” A module with further information will display, prompting you to pick “Delimited” or “Fixed width.” By selecting “Delimited,” you may instruct Excel to divide the content in the cell anytime it encounters a space, period, @, or other character. The “Set Width” option instructs Excel to divide the data at a fixed place for all cells. In this situation, since the cells include separate first and last names, I’ll choose the “Delimited” option and instruct Excel to divide the information as soon as there is a gap. Choose “Delimited,” then click next and drag the preview’s arrow line to the area of your future second column. Excel then displays a preview of the outcome. If everything is in order, click “Next” and then “Finish.” There will now be a column with the name and another column next to the first name.
7. Use these formulas to do simple calculations
Simple Excel Tricks Excel does not only do sophisticated calculations; it also assists you in performing basic arithmetic tasks. Excel may be used to do additions, subtractions, multiplications, and divisions.
Keep in mind that in order for Excel to do an automated calculation, you must always include the symbol = before your digits.
– To perform an addition, use the symbol +, as in “=10+10.”
– To subtract, use a sign, such as “=10-10.”
– To multiply, use the * symbol, as in “=10*10”.
– To divide, use the marker /, as in “=10/10.”
You may also use parentheses to make certain computations happen first. For example, in (10+10*10), the second and third tens were multiplied together before the additional ten was added.
When we perform (10+10)*10, the first and second tens are added together first.
If you wish to calculate the average of a group of integers, use the formula =AVERAGE(cells).
You may use the formula =SUM to sum a column of numbers (Cells).
8. Color a cell based on its content
Simple Excel Tricks Conditional formatting adjusts the colour of a cell dependent on the information contained inside it. Nothing could be easier, for example, than to highlight the earliest dates of birth in red and the most recent in green. This helps you to rapidly view the key facts. To begin, choose the cell group on which you wish to utilise the conditional shape. Then, from the home menu, pick Conditional Formatting and then select your settings from the preset drop-down list.
If you wish to make choices other than those provided by Excel, you may write your own rule. For instance, if you wish to colour all “GILLOU” names red, go to > “New rule.” A popup will then appear, prompting you to submit further information about your formatting rule. When you’re finished, click “OK,” and your formatting will be applied immediately.
9. Use the “IF” formula
Simple Excel Tricks The “IF” formula is the gold standard for creating logical tests. It lets you to tell Excel, “If a cell fulfils such a requirement, then enter such and such a result.” If the comparison is true, the first result is applied; otherwise, the second result shows. The formula’s syntax will be as follows: IF(Cell to test=”X,” Result if true, Result if false) A very basic example is our database of surnames, first names, and birth dates. Assume I want to inform Excel that if a tested cell includes the first name “GILLOU,” it should insert “Yes” in the destination cell and “No” if it does not. Nothing could be easier than that. Before any formula, I always start with a “=”. I placed IF(the tested cell=”GILLOU”;”YES”;”NO”) after the “=”. If the test succeeds, the result is “Yes,” and if the test fails, the answer is “No.” As a consequence, I get the Yes or No answer for each cell checked automatically. Of course, I may substitute other terms for “Yes” or “No.”
10. Use the dollar sign $ to move a formula easily
Simple Excel Tricks Have you ever come across the symbol ($) in an Excel formula? It has nothing to do with the US dollar, but it is as vital! It assures that a cell’s reference becomes “absolute” rather than “relative.” Yes, I realise what I’m saying is a little Chinese. So, let’s look at an example. If I put the formula “=A1+A2,” the outcome will be determined by these two cells. When I drag this formula into the cell below, my calculation references will now be “=B1+B2.” However, I want cell “A2” to be an absolute cell since this data serves as a reference for all of my computations. It should never be altered. So, in order to keep the cell reference constant, I’m going to enter my formula dollars at the level of “A2.” “=A1+$A$2” becomes my formula. As a result, when I drag my formula, cell A2 will always remain in my formula. To add dollars, in my example, click “A2” and then hit “F4” on your keyboard.
11. Use VLOOKUP to Find Data Easily
Simple Excel Tricks The VLOOKUP function in Excel is one of the most helpful and handy utilities. It enables you to locate any data in a table and move it to another spreadsheet. Assume you have a table containing a person’s first and last name but no date of birth. This date of birth is in a massive database where manually searching for the information would take a very long time. VLOOKUP is here to help! The formula’s syntax will be as follows: VLOOKUP = (the search reference; the lookup table; the search column number; approximate value or exact value). This is the same value that you have in both worksheets for the lookup reference. As an example, consider the individual’s name. For the lookup table, this entails picking the complete table containing the data. For the number of the search column: in the database, I identify the column containing the information on the dates of birth (column No. 3 in the example). For the approximate or precise value requirement, enter “False” to get the exact value and “True” to find a near value. Please keep in mind that this method only works if the search reference in both databases is same!
12. Automatically count the number of times a value appears
Simple Excel Tricks The COUNTIF function can greatly simplify your life! Instead of manually counting the number of times a certain value or number appears……use Excel’s COUNTIF function to perform the job for you. Assume I want to count the number of times the name “GILLOU” occurs in my table. > “=COUNTIF(range, criterion)” will be the formula syntax. There are two variables in this formula: The first “Range” is the range that the formula should cover. We’ll utilise the “A:A” column in our example since we’re only interested in one column. Then comes “Criterion”: The requirement in our case is “GILLOU.” Then, in any cell, enter the formula ” =NB.IF(A:A;”GILLOU”) ” and press “Enter” to see how many times the word “GILLOU” occurs in the whole range tested.
13. Combine cells using “&” symbol
Simple Excel Tricks Databases often partition data in order to detail as much information as possible. Instead of a single cell indicating a person’s first and last name, you have two: one for the first name and one for the last name. Except that you want to have just one cell containing the person’s name and first name. As a result, the two cells must be combined. Place your cursor in the blank cell where you wish the entire name to display, then type > “=”. Then, highlight a cell containing the first name, enter a > “&” symbol, and then highlight a cell containing the final name. But you’re not finished yet. There will be no space between the person’s first and last name if you merely enter > =A2&B2. To add the required space, type > =A2&” “&B2. The quote marks surrounding the space instruct Excel to insert a space between the first and final names. As a result, the formula containing our example’s variables will be: =A2&” “&B2. Simply slide the corner of this initial cell down to perform this formula on many lines.
14. Easily add a website link
Simple Excel Tricks If your employment requires you to monitor social networks, it is useful to include columns in your database that contain the web URLs of the sites. When you enter the URL addresses of the sites in question to Excel, the links usually take you to the address of the site simply by clicking on it. If, on the other hand, you enter “Journal le Monde” in a cell and want it to take you to the site’s URL, you must use a hypertext link. This is how. Choose the words you wish to hyperlink and hold down the Shift “K” key. After that, a box appears in which you may enter the URL for the hyperlink. Enter the URL into this area by copying and pasting it. Don’t get too worked up if the keyboard shortcut doesn’t function. Manually manipulate the cell by highlighting it and selecting Insert > Hyperlink.
15. Add a drop-down menu
Simple Excel Tricks The ‘Drop-down menu’ function in Excel is a must-have for tracking down operations. For example, rather of continually entering words in your sheet, such as “Yes”, “No”,…, you may utilise the drop-down menus to easily choose the correct answer without having to retype it each time. It also helps to reduce input mistakes by restricting users to just a limited number of replies. Here’s how to include drop-down menus into your cells. Choose the cells where you want the drop-down lists to appear. Then, in the top navigation, go to menu > “Data” and choose “Data Validation.” You will then see a data validation settings box popup. Select the source where the items you wish to show in your list are found by clicking on “Lists.” When you click OK, your cell will be richer with a drop-down list.