we've all been there it's the start of a new month and you're now faced with the overwhelming task of updating your Excel files and reports it's going to take hours if not days to complete but it doesn't have to in this video I'm going to show you five Excel tricks you can use to automatically perform common tasks like including new items in drop- down lists updating ranges for formulas pivot tables and charts as well as consolidating data and creating Dynamic text labels so you can get your work done 10 times faster here I have my monthly budget versus actual spend report by the way you can download this template and see the step-by-step video from the link in the video description now part of the process of updating this report is to classify my transactions into subcategories and to ensure I'm consistent so that the data doesn't make a mess of the report I have a drop- down list set up where I choose the categories now you're bound to need new subcat categories from time to time for example here I purchased a monthly travel card for public transport but if we look at the list of subcategories I don't really have a subcategory that's suitable now this drop- down list references my categories table on the category sheet and you can see here's the subcategory column so all I need to do is add a new subcategory here for public transport and it'll have the same category and category type as taxis so with that added if I go back to my transactions table click on my drop- down list you can see public transport is now available so I can select it and that job is done okay let's switch back to the example file and I'll show you how to set up your data validation lists so that they automatically include new items here I've got a subset of my data where I need the new subcategory bought in for public transport by the way I brought in the category and category type Fields here using X lookup so it looks a little different to my budget versus actual file we were just looking at and these are looking up my category sheet where I've got my list of subcategories and how they map into the category and category type the first step is to format the categories data in this table in an Excel table and it's easy to do VI the insert tab table or with the keyboard shortcut contrl T now my data is already in an Excel table so we have the table design tab available on the ribbon and if you look in the table name field you can see I've renamed it TBL categories you can just type in there and give it a new name which will make it easier to reference next I need to define a name for my subcategories so with them selected on the formulas tab of the ribbon Define name I'm going to call it subcategories and notice in the refers to it's referencing the table and the subcategory column I'll click okay and then then back on my transactions table I can set up the data validation lists for my subcategory column and we do that on the data tab of the ribbon data validation here I want a list and in the source I'm going to reference the defined name I just set up for the subcategories now if you can't remember what you named it press the F3 key it brings up the list of names select it click okay and then click okay again and now if we click on the drop down you can see I've got data validation that I can choose from however I haven't added the name for my public transport subcategory so let's do that and we'll copy down the category and category type now if I go back to my transactions table control down arrow to the end click on the drop- down list scroll to the bottom there's my public transport subcategory it automatically brings in the category and category type and that job's done now the key to this working is to First format your categories in an Excel table then Define a name for the items you want in your drop-down list and then use that name in your data validation list source and then any new subcategories you add to your categories table will automatically appear in your drop- down list without you having to lift a finger to edit anything if part of your report update process involves new data in CSV text or Excel files like I have here in this folder you can automate Gathering the data into a single table that feeds your formulas pivot tables and charts with one click I'll open one of the files so you can see the data now each file has the same structure and that's because they're exported from another system and that's going to allow me to append them to one another let me close it down and then in my example file I'm going to import the data from the data tab the ribbon we're going to get data from file and then from folder and this is power query all I need to do is navigate to the folder containing my files and click open now we don't see the files here but you'll see them in a moment power quer is found the list of files now we can see these are Excel files but they could just as easily be CSV or text files and at the bottom I can choose to combine and transform the data combine and load it or combine and load to which me to choose where to load it to I'm going to make some changes to the data so I want combine and transform data here I need to specify where my data is it's on sheet one and we get a preview of it I can click okay and this is going to open the powerquery editor you can see on the left the queries that power query is automatically written for me when it Consolidated those files I've got the data query selected that's the last one and that's the one I'm I'm going to work with so I'm going to collapse the queries pane now if we look in the source name column you can see it's got the file names and if I click on the drop down it's got the five files so I know that my five files of data are in this one table now I don't actually need the file name in my data set so I'm going to press the delete key to delete that column and then if we scroll across you can see I've got order quantity and unit cell price just holding down control to select them both but I don't have a column for myself sale amount or we can add a column clicking on the add column Tab and then we're going to perform a standard multiplication of these two columns that I have selected this adds a new column here let's double click the header to rename it sale amount and press enter and then I'm going to left click and drag and just pop it in beside my unit sell price let's click in the name for the query and we'll call this sales data and if you look Below in the applied steps pane you can see Power query has recorded all the steps I've taken to get to this point so next month when I get new data I don't have to repeat any of these tasks okay now that I have my data from all the files in a single table I can go to the Home tab and under close and load I want close and load to and here I can choose to load the data to a table in my current file or direct to a pivot table report a pivot chart or just create a connection now if I load it to a pivot table report or pivot chart this will be more efficient for Excel and keep your file size smaller than if you first load it to a table in the file and then build a pivot table for example however if you need to reference the data in formulas then you'll probably want to load it to a table in the file so for this video I'm going to do that and I'm going to put it on an existing worksheet that I've got set up ready for it and click okay it opens the queries and connections pane on the right I'm going to close that down I don't need to work with it and from here I can easily summarize and analyze my data and if we look at the order date column you can see I've got data for January through May and a bit later we'll look at how easy it is to add June's data but first let's look at how we can leverage tables to automate other parts of our work now that our data is in a table it comes with readymade Dynamic named arranges called structured references which are made up of the table name and the column name these structured references automatically expand and contract to include data added or removed from a table meaning we never have to edit a cell reference in formulas pivot tables or charts again for example let's build a quick pivot table and I'm going to pop it on an existing sheet that I've set up ready for it and I'll click okay let's left click and drag the pivot table field list out it's close to the pivot table so here I want to see the ship mode in the columns I want my order date in the rows and let's look at the sale amount all right that's done I'll close down the field list I'll right click here and go into number formatting and we'll just fix the numbers so they've got comma separators and no decimal places click okay that's better now I can also visualize this in a chart so let's insert a line chart and I'll bring it underneath let's just tidy this up I'll remove all the fill buttons cuz they just take up a load of space and let's put the legend at the top and we'll get rid of grid lines all right that'll do for now and then to demonstrate the formulas let's insert a formula that calculates the total sales so we're going to sum we're going to go back and reference my Consolidated data so I want to sum the sale amount just going to left click in the column header and it selects the sale amount column close parentheses on sum and press enter that's that done and let's also add one for the total unit sold and here instead of referencing the table with the mouse you can also just type in the name of the table so there is their sales data tab to select it open square bracket for the list of columns and I want the order quantity tab to select it close square brackets close sum and that's that done let's fix the formatting we'll get rid of the decimal places and add comma separators now let's pretend that next month has rolled around and we need to update our reports first I'm just going to copy these values and paste them as values with control shift V Let's also format them so they're easy to compare and then when we add the data you should see these values increase so here I am in the folder that contains my files I'm going to contrl V to paste in my June data file and then go back to excel let's go to the query output and on the data tab of the ribbon I'm going to click refresh all power query goes away and updates the table we can see June's data is now there let's go to the report and you can see that my formulas have updated cuz the values have increased however my pivot table still only goes up to May and that's because the pivot table did refresh but it refreshed before the query had time to deposit the data into the query table it's easy to fix just click refresh all again and now my chart and pivot table include jun's data now ideally you would load the powerquery data direct to the pivot table or pivot chart instead of a table back when we closed and loaded the query this way you'd only need to refresh all once for the pivot table and chart to include that new data and of course if you need multiple pivot tables and charts then you could just copy this pivot table paste it and then edit it as required and it would automatically refresh at the same time as you clicked refresh all tables and their structured references are all good and well but what if you can't use them for one reason or another in that case we can write Dynamic named range formulas that automatically grow and contract with our data just like structured references here I've got a pivot table that summarizes my expenses by subcategory with a slicer that allows me to filter what categories are displayed now I want to visualize this data in a tree map chart so let's insert tree map but notice we can't create tree map charts based off pivot table data which is annoying but not a showstopper so I'll start by just copying the datar crl C to copy contrl shift V to paste as values and then let's insert a tree mat chart based on this data let's do a little bit of formatting we'll get rid of the legend and we'll just skip the chart title for now so I've got my tree map but what I really want is for my chart to reference the pivot table so that I can use the slices and have it dynamically update the chart and this is where Dynamic named ranges come in handy let's right click on the chart and go into select data and we'll edit the legend entries and you can see I need a dynamic named range for my series values this is just referencing some of Nets so that's easy to change and if we edit the category labels you can see I also need a dynamic named range to reference the subcategories so let's close that and we'll go and write those formulas that we need I'll just write it here in this cell beside the pivot table and we'll use offset offset returns a reference to a range of cells so it can be used anywhere you need cell references but it allows the reference to adapt just like a structured reference the first argument for offset is the starting reference cell well that will be my first cell in my subcategory list I'm going to F4 to Absolute that comma then it wants to know how many rows from that reference you want to move up or down I don't want to move at all that's my starting point likewise for columns how many do you want to move left or right well I don't want to move left or right at all so I'm going to skip that argument then it wants to know how high the range should be well I can use count a to count how many subcategories I have and I'm going to extend past the end of the pivot table in case I add more subcategories in the future let's F4 to Absolute that close count a and then the width well it's just one column wide so I can skip that argument as well that's my offset now when I press enter it's going to Spill the list of subcategories which is fine but it's actually returning a reference to a range of celles so with that done I'm going to copy the formula and then on the formulas tab I'm going to define a name for that formula and we'll call this dream map AIS and in the refers to I'm going to delete what's there and contrl + V to paste in my offset formula click okay that's done now I also need one for my values so all I need to do is change the starting point from column C to column D and now I've got my values let's copy that formula we'll Define another name this will be my tree map values and in the refers to going to paste in the formula click okay now I don't need offset here anymore let's select the tree map and we'll go in and apply those names we've just defined so select data I'm going to edit my sum of net amount so here it's currently referencing the data behind the tree map chart this is just the series name so we can just reference the cell in the pivot table that's fine and then in my series values this is where I put in my Dynamic named range now it needs to be prefixed by the sheet name so I'm just going to delete the cell references and then F3 to bring up the list of names this is my tree map series values so I want tree map values click okay and okay and then we'll edit the category labels again I need to delete the cell reference part of the range and F3 and this is is my treap access click okay and okay so we can see the access labels are still there it's looking good click okay and now you can see that my tream app is actually referencing the pivot table so we don't need these text values here let's delete those and then let's test if it works I'll click on living expenses and my trap updates let's select a few different categories and you can see it's updating as my pivot table changes and if I click on the chart you can see the range only references the values that are displayed in the pivot table perfect before we dive into the next trick I want to take a moment to commend you on making the time to learn some new skills if you're finding these tips helpful and you want to dive deeper into mastering Excel consider checking out my courses at my online trining hub.com these courses are designed to fast trck you to becoming an Excel Pro with in-depth tutorials and Hands-On exercises as well as support from me personally they cover everything from the basics to Advanced Techniques so whether you're looking to improve your data analysis skills automate tasks or create stunning dashboards there's a course for you the link to the list of courses is in the video description and pin comment back in my accounting days one of my monthly tasks was to analyze variances and write commentary to explain them it was manual and laborious I wish I'd known then that you can create Dynamic text labels that automatically update let me show you how here I have a chart that tracks actual versus budget and you can see I currently have actual data up to September but it's difficult to see from the chart how the variance tracks year to date we can see in the table the cumulative variance is 9k now I could use my chart title to add this context to the Chart but then I'd have to manually update it every month and that would be a pain alter ably I can write a formula that creates this title automatically and updates as I add new data to my table let's build the elements separately so it's easier for you to follow the different steps and challenges along the way and then we'll bring it all together so first I need to locate the current month and we can use index to index the list of months and then index wants to know what row number I want returned well we can use match to find the last value in the actual column so the lookup value needs to be bigger than any of the values I'm going to have in this lookup column so I can use the scientific notation for 10 billion which is 1 E1 comma the lookup array are my actuals and then in the match type I want to find the value that's less than the lookup value so that's one and that will return the last value in the column close match close index and press enter so you can see it's returned a number and that's because if I click in my month column you can see it actually contains dates that have been formatted just to show the month name so what it's returning is the date serial number for September so what I need to do is format the value returned by index and we can use the text function to do that so the text function takes a value that's my date serial number comma and then the format I want for that date serial number well in this case I want to return the whole month name which is 4 M's close parentheses on text and now we have September okay that part's done next I want to append some text to the month name and that text is going to be variance to date colon and then a space close double quotes So I've got September variance to date next I need to find what that variance value is so again we can use index move that out the way to index the cumulative variance comma and what row is that on well we'll use match again to find a very big number that it can't find where is it finding it in the actual column and it's going to return the value less than the value it can't find close match close index and you can see it returns 9k that's great but it'd be nice if it formatted it with a dollar sign and a k so again we can wrap in index in the text function and the format is going to be the dollar sign and then hash comma # Z and then with a K now I also want a format for the negative value so I'm going to put in the semicolon and then I'll copy that type in a minus paste in the same format close double quotes So now it has the format for the positive and negative values close the text function press enter and there's my formatted number so now I've got the two components of my Dynamic title let's open up the clipboard or clear it and then I'm going to copy these two formulas contrl C to copy adds it to the clipboard and then let's go in and we'll copy this as well I'm not going to copy the equal sign for this part copy and then I'll delete them I don't need them anymore and then let's go and paste them in here so I'm going to click to paste the first part of my formula and then and and click to paste the second part press enter there's my Dynamic title all I need to do now is link it to my chart title so with the outer edge of the chart title selected click in the formula bar equals select the cell containing your label and press enter now the chart title doesn't look any different but let's enter a value for October let's say it's 25 press enter and I done Dynamic title has updated perfectly now of course you could use this technique for different types of dynamic text so consider using it next time you're preparing reports once you've mastered these five tricks and freed up some time you'll be ready for even more Excel tips and tricks in this video I introduce you to some gamechanging tools that most users tend to overlook I'll see you there