After years of using Excel in a corporate environment here are my top 25 Excel tips and tricks 2019. Welcome to FlashTutorials.in. I’m about to show you some useful tips and tricks for Excel 2019. These tips and tricks have made me more productive and I’m sure they will make you more productive as well. Now let’s get started.
Following are the List of 25 Excel Tips and Tricks
1. Quick Analysis Tool:
Most people aren’t aware of the Quick Analysis tool. When you highlight any table, in the bottom right corner is an icon. This is the Quick Analysis tool menu. If you click on that it gives you the wealth of options you can choose from. To change your table, for example, I can click on Totals. Click Sum and it shows the sum of the columns. You can click this sum and it sums the rows. You can do formatting on the table, add icons, create charts and insert them into your spreadsheet. There’s so much available here, and it makes quick and easy to avoid going through the menu. For inserting each one of the different options onto your table.
If you are not using the filter you should be. It’s very simple. click anywhere inside of a table of data. Go to the Data tab, click this Filter icon, and it creates a filter at the top of each one of the columns. When you click on these it shows you all the unique values listed anywhere in that column and you can turn them off. Select one in particular or two or three or as many as you like. Hit OK and it filters out only those rows of data that matched that filter. It’s a great way to manage large lists of information.
3. Drop-Down Lists:
Say you want to select a list of values like 1 2 3 4 and you want it to be select from this column right here. Go to the Data tab, click on Data Validation, and choose List from the menu choice. In the source select the choices that you want to make available and hit enter. Make sure this in cell drop-down checkbox is check and hit OK. Now when you are in this column and you hit the down arrow it gives you those four choices to choose from. If you try to type something that is outside of that range it gives you a warning that the value doesn’t match the list.
4. AutoFit Column Width:
This is by far the quickest way to adjust the width on your columns. Go to the space between columns so your cursor turns to this shape and double click. It will readjust the size of the columns to fit the widest point of the data anywhere in that entire column. You can also highlight all the columns, click on any one of them the same way, and it will do many columns at the same time.
Let’s say you want to reverse the columns and rows in a table. All you have to do is highlight the entire set of data, right-click, copy. Choose the location where you want to place the results. Click Paste Special, and check this box Transpose, and hit OK. Now all the columns and rows have been reversed.
Removing duplicates is handy. If you have a list of names or other information which are duplicates and you want to end up with a unique set of values. In this example, I’m gonna highlight this list, click on Data, choose to Remove Duplicates. The columns are select – first name, last name, and the amount. In this case, I’m going to hit OK to remove the duplicates where all three of those columns match. That should be true for row 3 and row 8 and as you can see it removes the 1 duplicate value as shown in the video.
7. Goal Seek (Refer Video):
Goal Seek is an advanced function in Excel and is part of the What-if Analysis tools. In this example say we have many items that we sell. We know how many quantities that we’ve sold of the first two items and the price for each and what the total dollar amount. But for the third item, we want to know how many of these we need to sell to get the total to $6,000.
Well, you can punch in numbers right here to try to figure out. Then keep working it until you get to a number that matches the 6000 that you’re targeting. But you can also use the What-if Analysis to figure it out for you.
To use Goal Seek you want to start by clicking on the target cell, go to Data, What-if Analysis, and choose Goal Seek. It fills in the target cell as your Set Cell. The value we’re trying to reach is 6000 and the cell we’re going to change is the quantity for Item 3. When you hit OK it goes through and calculates 197 as the magic number to reach that goal of 6000 total.
Be aware that you can use your imagination to come up with all kinds of scenarios for the What-if Analysis using Goal Seek. It handles very complex solutions.
8. VLOOKUP (Refer Video):
VLOOKUP is a very used tool to find data in a list. In this example, I have a list of names with an associated ID. Over here I’m creating a new table and I want to reference the names in this table to look up the ID from this list and fill them. To do that I want to use the VLOOKUP function.
Click on the insert function button and you want to find VLOOKUP in the list. You can type it in, do a search against all and once it’s there select it and it brings up the function arguments. To remind you of what you fill in, for each one of these there’s a description down here. The lookup value is what value do I want to look up in the list, and that is the name Nacy. The list that I’m going to choose from is the entire list and the Column Index Number is the column that I want to return the value from.
In this case, this is column 1 and this is column 2 so I’m going to choose column 2. And finally, the range lookup is either True or False. If you use True it looks for the closest match. False is an exact match and in almost every situation, you want to use False. Hit OK and you can see that it looked up Nancy in the table and found the ID and returned it over here. All we have to do then, let’s copy this data down.
Now you’ll notice right here Carol isn’t in the list so it returns an N/A. Now note that there is an HLOOKUP function which is horizontal instead of vertical. So if you’re looking up something in a different format you may need to use the HLOOKUP but it has the same parameters. The beauty of the VLOOKUP function is if you change a value it will update it in the table.
9. Flash and AutoFill:
Flash Fill and Auto Fill are different. In this example, we have some email addresses which fit the format of first name dot last name throughout the list. If you start typing the name because you want to pull it out of the email address. Flash Fill detects the pattern that you’re using and it recommends a solution to fill it in for you. So you can hit enter and it fills those in for the rest.
Auto Fill is also based on patterns so if you put a number in, for example, you use the right corner and drag it down, it auto fills those numbers in there. Now if I did 1, 2 and selected both and then drag it down sees the pattern and increments by one each time. You can do the same thing with dates, with months.
Remember there has to be a detectable pattern and to use the correct option on the corners. So if you type something in and you drag it, it’s gonna copy it. If you type something in and you double-click it fills it in, matching up with the left column next to it. And if you define a pattern make sure you highlight both or more and then drag. Flash Fill and Auto Fill are both good at making you more efficient when typing information in.
10. Paste Special Values:
Paste Special Value is a handy tool to use if you have a bunch of data with formulas. You want to copy all that information over to another location and get rid of all those formulas.
So you can see on this I have a formula that adds Band C together into a full name. I have a formula that divided for the percentage on this. And if I want to take those things and remove them and copy this to another location so I am left with the data. All you have to do is highlight, Copy, pick a new location, and do Paste Special. In the list choose values and hit OK. Now you’ll notice when I highlight these there are no formulas. It’s the value of the data.
You’ll find this more common than you’d think. Because often times you want to remove all that formula and formatting and use the data in a different way. This is a quick function to help you do that.
11. Images in Charts:
It’s simple to spice up a chart with images. I’ve created a bar chart from a set of data. If you come into the chart and click at least a couple of times to bring up the Format. Click on this Fill & Line icon. go under Fill and there is a Picture or Texture Fill option. If you select that, you can pick from a file and choose an image. It will fill that image in on your chart in place of the normal texture.
12. IF function:
The IF function is very handy if you want to do conditional data representation. For example, in this chart, I have a column ABCD, a column with numbers. Some of those numbers are greater than 10 and some are less.
I want to create an IF function to determine when these numbers are greater than 10 and say " BIG". if they’re less than 10 I want to say " SMALL" so I click on the Fx. Type in IF, choose All and do Go to search for it. Bring up the IF function. The first parameter it asks for is the logical test. What we want to know if this number is greater than 10.
If that’s true we want to print " BIG" If it’s false we want to print " SMALL" Ht okay. There you have your answer.I’m going to copy this down to the other ones and now I can see the one that’s small.
13. Insert Screenshot:
If you want to insert a screenshot onto your excel page. Go to Insert tab, click on Illustrations, and go to Screenshot. It will show a list of active screens from other applications. Select the one you want and it inserts the image onto the page. You can then adjusts the sizing but you want and you can also come up to the Crop and crop out portions of the image.
14. Absolute Cell Reference:
Excel uses two types of referencing relative and absolute. As you can see on this chart C4 times D4 takes the quantity times the cost and gives a result. This is a relative cell reference as I look down the list. it’s referring to the second one to the left and the first one to the left in the formula. The Total is also a relative cell reference because it is showing E4 minus F4, the discount. And as you move down the list it’s referring to the one to the second left and the one to the left in the formula.
Now if I was to add a discount in here, The formula would be this number times this number. This is a relative cell reference reflecting the one to the left times this one up here.
The problem is when I copy this down. It shouldn’t be pointing to this number times G2 because there’s nothing in G2. We want it to stay G1 so we need an absolute cell reference.
To do that all you have to do put a $ in front of G and a $in front of 1 to force that to be a fixed value to that cell. And now when we copy these down, there’s G1, there’s G1, there’s G1 and there’s G1 as well. That is an Absolute Cell Reference.
15. Show Formulas:
Often a spreadsheet gets to the point where you have many formulas spread out all over the place. It’s difficult to look at each one. Wouldn’t it is nice if you could see a list of all the formulas all at once?
Go into File, Options, Advanced and scroll down toward the bottom. There is a quot; Show formulas in cells instead of their calculated results & quot. If you check that box and hit OK it now actually shows all the formula references for you to view.
Another option you have is double-click on a cell and you can see the color codes of the reference cells that are being used and the formula for that cell. Those options make it easier to see formulas at a glance.
16. Text to Columns:
When using Excel you’ll find yourself wanting to copy data from other applications or from a web page or other source.
I have a set of data in Word. If I copy this and paste it into an Excel spreadsheet. Unfortunately, it copies all as one line and we want it to break all these components out into different columns. That’s the whole purpose of using Excel.
So there’s a quick way you can convert these. highlight them, go to the Data tab, and click on Text to Columns. Now in this situation, you can choose between Delimited or Fixed Width. These are separated by commas so I’m going to pick Delimited and I’m going to choose comma and not tab. You’ll notice down here that it knows where the spacing is between each column. Hit next.
You can change some of the formattings if you want to and when you’re done hit Finish. Now it separates them into different columns. remember there has to be some delimiter to separate them out, or if it’s fixed width you can choose that as an option too.
17. Conditional Formatting:
You saw a sneak peek of the conditional formatting when we looked at the Quick Analysis tool earlier but there are more options available from the menu.
Let’s say on this table we want to show all the entries that have greater than 2000. highlight the list on the Home tab, choose Conditional Formatting, Select Highlighted Cells Rules, and choose greater Than, and enter 2000. Now it has highlighted everything greater than in 2000. if you want to turn that off go back to Conditional Formatting and Clear the Rules. There are many other options available here.
You can show the Top 10%, you can change Data Bars so you have a visual representation. You can change the color Scales for a more visual representation. I particularly like the Icon Sets and you can even define a new rule and make up all kinds of options to select what you want. It’s very powerful. So give yourself a better representation of your data through visual and highlighted items using Conditional Formatting.
PowerPivot is a free feature that’s installed as an add-in in Excel but it’s not installed by default. To enable it to go to the File, Options, click on Add-ins, and come down here to the bottom and choose ComAdd-ins and hit Go.
You’ll see in the list Microsoft PowerPivot for Excel. Check that box and hit OK and it will add this PowerPivot tab onto your screen. When you click on that tab you have a whole new set of features available to you.
Now without going into a detailed explanation of how to use PowerPivot. Because you can find that information elsewhere on other videos. The bottom line is this tool allows you to connect to other Oracle or SQL data sources and a variety of other data sets. Use it as a BI tool for large sets of data.
In fact, it expands the 1 Million row limit of Excel to unlimited numbers. It is mean to turn Excel into a business intelligence analysis tool so take a look.
19. Freeze Panes:
Freeze Panes allows you to block certain portions of the screen. In this example, I have a large set of data. When I scroll to the right I lose the first column name information and if I scroll down I lose the header information.
To solve this you can use Freeze Panes. Click on the corner where you want the rows above and the columns to the left to be lock. Go to View and click Freeze Panes and choose Freeze Panes from the menu. Now when you scroll right you can see the names in the left column and when you scroll down you can see the rows in the heading. To undo the freeze panes click on it again and click Unfreeze.
20. Control Arrow Keys:
Don’t forget to hold down the Ctrl key when you’re narrowing around a set of data. Ctrl right-arrow, down-arrow, left-arrow,up-arrow. It’ll move to the end, beginning, bottom, or top of the data set that you’re working within.
21. 3D References:
If you’re tracking data over a period of time. It’s often common to create a different month tab for each one of the sets of data. In this case, I have September, October, and November. They are identical the format but the data is different in each.
Let’s say I want to create a total of all those. I can hold the Ctrl key down and drag this tab and then rename it to get our Total tab. Let’s say we want to add a heading to the top of each one of these charts. We can add it in and go into each one of the tabs one at a time. But, with 3D Referencing all you have to do is click on one end, hold down the Shift key, and click the other end. Now you’ve highlighted all four of these tabs. Go ahead and do your insert, put your heading, make your changes, and now when you click on the individual ones you’ll see what changes I made on all four.
Now let’s take our total and let’s use a 3D Reference to add the quantities from each of these three tabs into this cell. I’m gonna hit the Sum. I’m gonna go to the first tab, choose that cell, hold down the shift key and select all three and hit Enter. Now that you’re on the Total tab, you’ll see that it’s summed September through November. Now I’m going to drag these down, copy them across and you have the total for all three. That is 3D Referencing.
22. Forecast Sheet:
Do you want a quick peek of the future? So you’ve got a set of numbers here that look like they’re in a pattern. All you have to do is highlight that, go to Data, and choose Forecast Sheet. This gives you a trend line and the forecast. You can click the up-arrow here to extend it out for a longer period of time if you want to look farther in the future. That’s a quick look at the Forecast option for something simple.
23. SUMIFS Function:
The SUMIFS function allows you to do conditional summing of data. So in this example, I have a table with month, item, and amount.
Over here I’m going to add the sum of the amounts based on these criteria. Entering the SUMIFS function we do =SUMIFS and the range is the range of values that you’re summing.
The criteria for this first example are the month. So we’re going to select the month range and the value we’re looking for is right there. You can see the total is 68 which is the sum of these first three. For this, we’re going to do the same thing. The range is still the values, criteria that we’re selecting for first in the month, with this being the month. We can continue on with another set of criteria. Have it be the item, and this is the specific item we’re selecting and you can see 78 is the total of May Items. And that’s the SUMIFS function.
24. IFERROR function:
The IFERROR function can be used to clean up some bad data. For example, I have a formula here that calculates the per item amount for the quantity and the total. If I copy this down I get a divide by zero error because the quantity on this line is zero. Well, the IFERROR function can be used to clean this up.
All you have to do is come in here, add IFERROR in front of your formula, and if that value is an error then what do you want to do? Let’s put in zero. Now when I copy that down it puts a zero in there but still calculates the other locations. And that’s a quick fix with the IFERROR function.
25. Filled Maps:
FilledMaps are like any other chart but way cooler. Take a set of data, highlight it, make sure you have location-specific information in here. Which can be a state, a city, a zip code, GPS coordinates, or any other location related data elements.
Go to inserts and chooses Maps. Select this Filled Map and your data elements will be placed on a map. In this case the United States. You can also add different chart elements like Data Labels. You can change the chart to different types of styles.