Microsoft Excel has many surprising features and tricks hidden in it which often regular Excel users also don’t even know about. In this blog, we are presenting the top 20 Excel tips and tricks that beginners and advanced users should utilize.
1.Turn Image into Excel Table with Smartphone
If you have a picture of a table and you want to copy that table into an Excel sheet, then instead of manually typing it, Excel allows you to just take the image and it will turn into an Excel sheet with the exact replica of the table. To do that, download the “Microsoft Office” app from Play Store or Apple Store and open it after the installation is completed. At the bottom, click on the button that says “Action” and then click the “Image To Table” option. Now take the image of the table from your camera, resize the image, and click “Confirm”. Your phone will show the Excel sheet that contains the same table and the entries in it.
2.Free Excel on the Web
You can use Excel for free on the web. To do that, go to the Office website and sign in to your account. If you don’t have an Office account, then click on “Sign up for the free version” to make a free account in Office. After signing into your Office account, click on the Excel icon from the left side menu bar. It will open up Excel and now you can use it for free on the web.
3.Use “Ideas” to generate Data Insights
You can make Excel analyze the data in the sheet and create graphical insights about the data. To do that, once you have completed the data entry, click on “Ideas” from the “Home” tab. This way, on the right-hand side you will see the “Ideas” section bar presenting insights based on the data in your Excel sheet. It also allows you to ask a question about your data. But make sure that when you are asking a question from “Ideas”, you are using the same column headers titles in the question so that Excel can find the right answer.
4.Set up Names for Cells
If you have to subtract one cell entry from another, then probably you will write the formula as “=A2-B2”. But Excel also allows you to name the cells so that it becomes easier for you to write formulas. To do that, select the cells you want to give a common name, go to the “Formulas” tab, and click the “Define Name” option. A pop-up window will appear where you can specify the name for those cells. Similarly, perform the same process for other cells. Now, if you want to subtract one cell entry from another, the formula “=Name1-Name2” will also deliver the same result.
5.Master Relative and Absolute Cell References
You can easily master relative and absolute cell references with a shortcut key. But, let’s first briefly look at what is relative and absolute references. If you want to subtract column A from column B and put the result in column C, then you can write the formula “=A1-B1” in the C1 cell and drag it down to change the formula with the cell numbers. This is known as relative referencing. But when you want subtraction to go as “A1-B1”, “A2-B1”, “A3-B1”, and so on, which means you want to keep B1 entry constant, then absolute referencing comes into action. To do that, in the formula bar go to B1 and click “F4”. You will see that B1 changes to “$B$1”. Now if you extend the C1 column, then the B1 entry will remain constant, while only the column A entries will change. Besides that, if you press “F4” twice, it changes from “$B$1” to “B$1”, pressing again will change into “$B1”, and pressing again will take it back to the initial form “B1”. This way, using the “F4” key, you can master relative and absolute cell references.
6.Macros
Macros are meant to automate repetitive tasks. To use macros, you first have to record macros in which you will manually perform the task and Excel will learn. To do that, go to the “View” tab and click “Macros”. Make sure you have checked the box “use relative references” if you want to do relative references. Afterward, click on “Record Macro”. A pop-up window will appear where you have to enter the name of the macro and fill out other options based on your needs and then click “OK”. Now Excel will learn all the activities you perform. So, do the task one time that you want Excel to do in the future. Once you are done, stop the recording. Now, to run that Macro, go to the “Macros” and click “View Macros”. Afterward, select the macro you just recorded and click “Run”.
7.Using Data Types
To use data types, click on the “Data” tab and you will currently see two data types, i.e., “Stocks” and “Geography”. But in future updates, more data types are going to be launched by Microsoft. How data types are helpful can be understood by a simple example. Let’s assume you have a column about “Country” and there are entries like Germany, France, Italy, etc. Now select those entries and then click “Geography” from the “Data Types” section. The geography icon will appear along with the entries. Now if you click on the “+” icon, you can get tons of data about those countries, such as population, major city, etc.
8.XLOOKUP Function
The XLOOKUP function of Excel looks for the array range and then returns a value based on the first match it gets. In XLOOKUP function, you have to define what value you have to look up, specific your lookup array, specific your return array, what to do if nothing is found, match mode, and search mode. For example, consider the function as “=xlookup(A5, $A$12:$A$13, $B$12:$B$13, “Not found”, 0). So, this function will look up the value A5 in the lookup array from A12 to A13. It will return the entries B12 to B13 if the lookup comes true or it will show the message “Not found” if the lookup comes false. The “0” in the function defines that you don’t want the match mode to work, as you are targeting the exact match.
9.SUM Function
Commonly for using the SUM function, we write “=Sum” and then select the column entries we have to sum. But you can also do it with a shortcut key. Highlight all the entries you want to sum and then click “alt” and “=” key. It will automatically show the SUM function with all the column entries added into the function.
10.Concatenate and Flash Fill Functions
If you have two text entries and you want to show up as one in the third entry, then you can use the function “=concat(A1,” “,B1)”. You can also do it in another easier way. In entry C1, write manually A1 and B1 and then drag them down to all cells where you are planning to do the concatenate process. Now click the “+” icon and then click “Flash Fill”. This way, Excel will intelligently join the text for all those entries based on A1 and B1.
11.SUMIF Function
You can also use the SUM function with a condition, which is named as SUMIF function in Excel. For example, you can have profit entries from Norway, Germany, and Poland, and you want to sum only the profits from Norway. Let’s assume that the country names are in column A, while profit values are in column C. So, you can write the function “=sumif(A2:A13,”Norway”,C2:C13)”. This way, Excel will first look for Norway from A2 to A13 and it will sum only the entries from C2 to C13 when the Norway condition comes true.
12.COUNTIF Function
Excel provides a COUNTIF function that helps to count a specific item from your Excel sheet. For example, you have a sheet showing which countries are selling which types of winter jackets, and you want to count the number of countries selling leather jackets. Let’s assume your countries are in column A and types of jackets in column B. Then the COUNTIF function will work as “=countif(B2:B10,”Fortune”)”. The output of this function will be a number showing the number of countries selling leather jackets.
13.IF Function
Excel provides the IF function to carry out a specific conditional check on the entries. For example, you are running a multi-national company and you get the Excel sheet showing the turnover in percentage from different countries. For the countries where you are getting low turnover, you want to see the status “Check”. Now let’s assume the turnover column is B in your Excel sheet. So, the IF function will work as “=if(B2<.25,”Check”, “OK”). Now if the B2 percentage is above 25%, then the function will return OK, while if the B2 percentage is below 25%, then the function will return Check. You can extend the function below to automatically apply for other column B entries.
14.Calculate Dates difference and use Convert Function
Sometimes you might need to calculate the difference between dates to know some useful insights, such as how many days your store remained open. To do that, let’s assume your cell A4 shows the date when you launched your store 02/20/2019 and your cell F1 shows today’s date 03/10/2021. You can calculate the date difference in cell B2 using the function “=F1-A4”. It will show you the number of days your store remained open. To see the number of years instead of days, you can use the CONVERT function of Excel. It works by feeding three values, i.e., “=convert(number, from_unit, to_unit)”. For your case, it will be “=convert(B2,”day”,”yr”)”.
15.Adjust Row Heights and Column Widths
The common way everyone knows to adjust row heights and column widths is manually adjusting them from the side scales. But there is one easy way to do it. Click the triangle-shaped icon on the left side near the start of column A. It will select all the entries. Now if you click on the “line” icon between two columns, it will adjust all columns’ width automatically. Similarly, click on the “line” icon between two rows and it will adjust all rows’ height automatically.
16.Freeze Panes
You can freeze the headers in Excel so that when you are scrolling, you are still seeing the headers. To do that, click on the “View” tab and then “Freeze Panes”. From there, you can freeze the top row, freeze the first column, or unfreeze panes. You can also freeze a specific row by first splitting that row and then freezing it. To do that, select the entire row, then click the “Split” option located next to “Freeze Panes”. Afterward, click on “Freeze Panes”. Now when you scroll down, that row will remain fix.
17.Pivot Tables
Pivot tables help to reduce the number of formulas you have to enter in the Excel sheet. To add pivot tables in your sheet, select any cell in your sheet, go to the “Insert” tab and then click “Pivot Table” located at the top right corner. It will select the data and then you click “OK”. Now from the right side in the pivot table window, you can drag your sheet columns to different sections to make a pivot table. You can also add charts for those tables, by going to the “Pivot Table Analyze” tab, and then clicking “PivotChart”. Afterward, you can pick the chart of your choice.
18.Drop Down Lists
You can also add a drop down list option in a cell so that users can just select the entries from the drop down list and fill the cell. To do that, first write the entries you want to show in the drop down list. For example, you can set the entries from G7 to G10 that you want in the drop down. Now go to the “Data” tab and under “Data Tools” click the Data Validation icon. A pop-up window will appear. From the Allow section, choose “List” and then from the “Source” section, select the entries G7 to G10 entries. Afterward, click “OK”. This way, you can create an effective drop down list in any cell and can drag it down to apply it to other cells.
19.Import Data from Web
You can easily import data from the web to your Excel sheet and make it continuously update as it updates on the web. To do that, go to the “Data” bar and click the “From Web” option. Now paste the URL of the web page and click “OK”. It will show you the navigator from where you can see different data you can import from that web page. You can edit the data from there before importing it into your Excel sheet by clicking “Transform Data”. Afterward, you can click “Close & Load”. This way, the data from the web page is imported into your Excel sheet. Now from the top ribbon, click on “Query” and then “Properties”. In the pop-up window, you can choose the option you like for setting the refresh rate of the data.
20.Collaborate with Others
If you have saved your Excel file in OneDrive or SharePoint, you can share it with others to let them view or even edit the file. To do that, click on the “Share” from the top left corner and enter the name or email address to whom you want to share the sheet. You can even set the options like allow editing, the expiration date of the share link, and set password.
So, these are the top 20 Excel tips and tricks. But as Excel is a gigantic tool, so there are still plenty of tips and tricks users can explore and use.
