7 Excel Tricks You Wish Someone Had Shown You Years Ago

There are certain Excel things that come up all the time at work, like controlling what people can type, keeping data organized, and building spreadsheets that update automatically when new data is added. You know, the stuff that should be basic, but here's the thing. Nobody taught you how. And now it feels weird to ask because shouldn't you already know this? So, you just keep googling or doing it the long way or avoiding it entirely? That ends today. I'm showing you the Excel tricks everyone needs to know, no matter what your job is. The everyday stuff that saves you hours but somehow never gets explained. And if you already feel confident in Excel, stick around for the final trick. It's one of those rare techniques that even experienced users almost never learn, but once you see it, you will immediately want to use it. As always, you can download the free practice file from the description and follow along. Let's go. All right, let's start with the single most important thing you can do in Excel. Convert your data to a table. And I don't mean just a range of data that looks like a table. I mean an actual Excel table. This one change will solve so many problems you didn't even know you had. Here's what most people do. They have their data in a regular range. Looks fine, right? But watch what happens when I add a new row at the bottom. If I have any formulas or a chart connected to this data, they don't update. I have to go and edit those references manually. And this is why many people find Excel frustrating. It's just fragile. Everything breaks unless you know how to set it up correctly. So, let's look at what happens when you format this data in an Excel table. Click anywhere in your table range. CtrlT is the keyboard shortcut. My table has headers, so I'll leave that box checked and click okay. Now, I can edit my group by formula to reference the table columns. Hover your mouse above the column name until it turns into the down arrow. Left click once to select the data. Left click again to include the column header. Let's do the same for the total sales. These are called structured references. And 6 months from now, you'll actually understand what this formula means. I'll press enter. It returns the same results. But now if I add a new row of data, the formula automatically includes it. And my chart connected to the group by data also updates. So new datas instantly included. And that's not all tables can do. With the table selected, we have the table design contextual tab available. And on here, let's say I want totals. No problem. Add a total row. Click on the dropown to change the aggregation type. Plus, every column gets a filter button. And on the table design tab, you can change the style from the gallery or create your own custom style. Hover over the options to get a preview. When you find the one you want, click on it to select it. We can also turn banded rows on and off, remove headers, etc. Pretty much whatever you need. So whether you're working with sales data, customer lists, HR records, etc., format it in a table. They're the foundation that makes everything in Excel easier. All right. Now that your data is in a table, let me show you how to make it interactive with slices. Slices are basically visual filter buttons, and they make your Excel file feel less like a spreadsheet, more like an actual app. I know we have the filter drop downs at the top and they work fine, but imagine you're presenting this to your manager or sharing this file with someone who isn't an Excel person. They're not going to dig through filter dropdowns. They want something simple, something they can just click. And that's what slicers do. To add one, select the table on the table design contextual tab. Click on insert slicer. Here you can choose columns that you want a slicer for. So I want sales rep, region, and product. Click okay. There's my three slices. Just hold down shift to select them and I'll move them roughly into place. This one can go beside and product over here. With them all selected, you can resize them so that they fit in the space that you have available. We might like to give them some more columns and color code them in keeping with your theme. Let's make this product one wider to fill the space. And we'll give this one three columns. Now the slices are connected to your table so you can quickly filter to see exactly the data you want. To clear the filters, click the red X in the top right. And you can select multiple items, hold down control and select each button in turn. And notice that the total updates based on the items that are selected. Slices are great when you need to present or share filtered data or even if you just want to toggle between different filtered views quickly. At this point, your Excel file already looks very different. We've got our data nicely formatted in a table, and any formulas or pivot tables connected to it will automatically update. And we've got slices that make it quick and easy to filter. And this is usually the moment people ask, okay, how do I turn this into a proper dashboard like this or this? If you want to learn how

to build dashboards like these and also understand when Excel is the right tool and when PowerBI is the better option, that's exactly what I teach in my data analyst FastTrack course. Inside that program, you'll learn how to clean and structure data properly, build interactive Excel dashboards, and create professional PowerBI reports. The focus is not just on features. It's on building complete reporting solutions from raw data through to finished dashboards. And since this is promo time, all my courses are 20% off until February 26th. I'll leave the link in the description so you can take a look. All right, let's keep going. Here's the problem. You're working in a file like this, lots of columns and even more rows. You click on a cell and now you're trying to trace across to see which row you're on or up to see which column you're in. Your eyes are doing this back and forth thing and it's just so easy to lose track. That's where focus cell which is available on the view tab for 365 users solves this problem. Now I can click on any cell and the entire row and column light up. Moving to a different cell, it follows me around automatically. And if you want to customize how it looks, click on the drop down and you can choose different colors so that it doesn't conflict with other formatting you might have in your worksheet. Here's a common problem. You have a list of full names and you need to split them into first name and last name columns. Most people would either do it manually, which is painful, or Google a complicated formula, also painful. Whereas FlashFill does it automatically. You just show Excel an example and then Ctrl E. It recognizes the pattern and fills in all the first names automatically. And it's the same for the surname. Give it an example. Ctrl E. It completes what it thinks is the pattern, but notice it's got this one wrong. So, let's correct it. and then it goes through and correctly fixes the others as well. Flashfill works for all types of data, so give it a go next time you want to reformat or split some text. The department and expense columns in this table have quite a few empty cells, which makes it difficult to reference this in a formula or build pivot tables from it. It would be tedious to fill these manually. Instead, select the cells that contain blanks. Ctrl G for the go-to dialog box. Click on special. Select blanks. Click okay. And now all my empty cells are selected. I can type equals and then up arrow and then controll and enter. And now I have labels on every row. And to ensure that they don't get messed up if you sort the data, Ctrl + C and then control shiftV to paste them as values. Now my data is in the correct format for easy analysis. Next, let's talk about controlling what people can type into your Excel files with data validation. Because if people can type whatever they want, you're likely to end up with a mess, which makes it very difficult to analyze and summarize your data. In this table, we have inconsistent department names. Excel sees these as completely different values, which means your filters won't work, your formulas break, and your reports are just wrong. We can use data validation drop-own lists to force people to pick from a pre-approved list. To set it up, select the cells that you want your data validation in. I'll select a few cells at the bottom to allow for more data to be added. And then on the data tab, data validation. And in here under allow, I want list. In the source, you can type in the items separated by commas or you can reference a list you might have in your worksheet. Click okay. And now I can only choose from approved items. So let's correct marketing there. And this one has typo. Ops should be operations and human resources should be HR. If I try to type something that's not in the list, I get an error and I can retry. Let's go back and choose the correct item. There are loads of ways you can validate what your users enter, including making sure dates are entered correctly. We can see here someone thinks there's 29 days in February this year. Let's put some data validation on this column to restrict what they can enter. Again, data validation here. We want date and I want dates greater than let's say 1st of Jan 2026. I can also enter an import message to guide the user. For example, valid dates are from 1st of Jan 2026. Use the format ddmm y. You can give it a title if you want. I don't think it needs it. And then in the error alert we can say this date is not valid. Enter a date from 1st of Jan 2026. Let's click okay. So now if you select any cell in this range, you get the tool tip popping up telling the user what they should enter. Let's try and enter one that's incorrect. Here we get the error. Gives us some guidance. Let's retry. 28 Feb 2026. And now it accepts the entry. Next, dynamic print ranges for PDFs. I

bet you haven't seen this before. This is perfect for when you need to print or create PDFs from Excel, like monthly reports or invoices where the data size changes every time. For example, here I've got an invoice. Sometimes it'll have three line items in the table and other times it might have 30. If I set a fixed print area, let's say we want to print this range of cells. One of two things could happen. You add rows to your table. It's going to push the whole invoice down and you're going to cut cells off. or you over select cells and you print too much and it just looks unprofessional. And if you're creating PDFs to send to clients, this looks sloppy. Let's look at how to create dynamic print ranges so the print area automatically adjusts based on how much data you actually have. The trick is to define a dynamic named range using a formula. We do that via the formulas tab. Define name. Let's give it a name. I'll call it print range formula. And then in the refers to, I'm going to use offset, but you can use index if you prefer. The offset function simply returns a reference to a range of cells. The first argument of offset is the reference. That's going to be the first cell in my range. And make sure it's absolute. That is, you have dollar signs before the B and the four or the column reference and the row reference. The next two arguments in offset ask you whether you want to move from cell B4 before you start your range. how many rows and how many columns. Well, I don't need to move at all, so I'm going to enter two commas to skip the next two arguments. Then, offset wants to know how high it should make the range. Well, we're starting on row four. I'm going to go down to row 40 to allow one blank row below the bank details. And my table currently has three rows. So let's take 40 that is the number of the last row minus three for the empty rows above the invoice template and minus three for the table height. This is our minimum number of rows in the print range. So that's 34. Then we're going to add rows for the table. This is a variable number of rows. So I'm going to use the count function to count the number of rows in the table price column. Now this column has a formula in it. So as I add a row, it'll automatically be included in the count. Close parenthesis on count. The next argument in the offset function is how wide it wants the range. In this case, the invoice is a fixed width at eight columns. But if your report has a variable width, then you can use count or count a again here to make it dynamic. So let's close offset. Click okay. Now, if you haven't already created a normal print area by selecting arrange on your worksheet, do that. So, I want to print down to row 40 and across to I page layout, print area, set print area. Now, let's go into the formulas tab and the name manager. This is where our defined name is. So, we've got our print area that we just set. You can see it's cells B4 to I40 and then the print range formula. Now to check that your offset formula that you've written in the refers to returns the correct range, simply click in there. You get the marching ants around the range. So we can see it's calculating correctly. Perfect. All we need to do now is substitute the print area refers to cell reference with our new defined name. So in here, delete that. A shortcut to insert the defined name is to press F3 and then grab it from the name list here. Click okay and check the box and close. Let's Ctrl P to check the print range being returned. So you can see it's got one blank row at the bottom and the rest of the data is all intact. Let's go back escape and we'll add some rows to this table just to check that the print range is adjusting. So we've added two more rows. The last row of our print range should now be row 42. Let's controll P. And you can see it's correctly expanded to include those new rows. We've still got the blank row below our bank details. No further editing required. A big shout out to Daniel Ferry for this very cool tip. Use this for templates like this, monthly reports, and anything you print or create PDFs for. Oh, and if you want this automated invoice template, the link will be in the video description. These tricks make Excel easy to work with, but the real speed comes from knowing the right functions. And that's why you should watch this video next. 15 Excel functions every professional should know in 2026. It's the perfect next step. I'll see you there.