Welcome to Part 3 of my Excel blog series. If you’re starting here, either you decided to skip the first two articles, as they were not advanced enough, or this is the first one you found. Part 1 gives beginner users of Excel some tips to help them get started. In Part 2, I dug just a little deeper for those users who have some experience to help work more efficiently or polish their reports a little more.
In this article, I go “all in” and tackle some of the more advanced items I use that will give your spreadsheets and reports a more polished look and feel. Now, this isn’t all about polish; some of these items will help you as you work in the spreadsheet and ensure information is stored as you expect it to be. Let’s get going.
You should be aware of the VLOOKUP() formula. I’m sure you have been faced with creating a spreadsheet that pulls data from many columns of data requiring you to update the column index value within the formula for each one. And you may even have faced this at a time when you had 100+ columns of data you needed to pull in. For the first year or two while I worked with Excel, I would manually update the variable in each of the formulas. That was until I found the INDIRECT() formula and the syntax is shown below.
It’s pretty simple to use with just one required value. The value ref_text is simply the text reference to a cell where you want to pull the actual value from. So just insert this formula within the VLOOKUP() formula as the column_index_num value. There is a little leg work to get the data prepared to use this formula in this manner, but very little compared to manually changing the column index value within 100+ VLOOKUP() formulas. Let me explain. First insert two blank rows at the very top of the sheet. This is where you will place the values needed for this to work properly.
In the first row is the reference to the cell with the actual column index value we want to look for. Do this using the following formula =CHAR(COLUMN()+64) &"2". What this formula gives me is the current column value plus the number 2 appended at the end, thus giving me the reference to the cell immediately below. Then in the cell immediately below I provide the column number that will be used in the VLOOKUP() formula as shown in the image below.
If you notice in cell B1, the formula will result in B2 as a value. Then in B2 there is a value of 1. In the VLOOKUP() formula, shown in cell B4 for the column index value, we are using the indirect formula to pull the reference value from cell B1, this will in turn point us to the value in B2 which would return a value of 1 as the column index value. That may sound like a really weird way of simply getting the column index value; and I would agree if you are only looking at a handful of columns, but if you have ever had to perform a VLOOKUP() formula and bring over 100 or more columns of data it is a time consuming and tedious process of updating this value in all of those formulas. Using this method allows you to create the formula in cell B1, and then simply copy it for as many rows as needed in a matter of a few seconds.
Next, simply plug in the column value in row two, again only taking about one minute tops. Then finally creating the actual VLOOKUP() formula. And as long as you set it up properly, you can copy this same formula over the entire sheet and just let Excel pull in all the values. When I came across this little trick it saved me unknown amounts of what was once wasted time.
If you have ever wanted to create a dashboard within Excel you know that real estate on the screen is very valuable. Also you should know a picture can convey what the data is stating so much better than giving the actual values. So you would probably prefer to have charts rather than data. With this combination of concerns, it can be a challenge to get the charts you would like displayed on a single dashboard. What if I told you that those charts could dynamically change by giving the user a scroll bar to interact with the data as they choose? This is exactly what I’m going to explain here. This does take a little bit to set up, so I will go through this a step at a time.
Just to give a little recap of how this all works, we are creating named ranges that dynamically change based on the current value of the scroll bar. Then we are taking these dynamically changing ranges and using them as the data and axis value within the chart. By taking advantage of this you can have a single scrollbar drive multiple charts at the same time, so that all the data being viewed is from the same date range. I have provided a spreadsheet named ScrollingChart.xlsx as a download that has this specific tip created within it. This was something that took me several attempts in order to pull everything together properly, and it can be confusing at times. So I felt it was important to give a sample for you to dig through and try out.
There have been times when I created spreadsheets that required the user to input data or allowed the user to input data to retrieve information. In those cases, I knew what format the data should be entered, but how would the user know this? By using Excel’s Data Validation you can do just this, and at the same time stop the user from entering any data in the incorrect format. Simply select the cells you wish to apply the validation to, and then on the Data ribbon, click on Data Validation. This will open a new window with three tabs to enter data into as shown below.
I touched very briefly on customizing a chart above, but I want to point out a somewhat new type of chart within Excel. This was introduced with Excel 2010 and is quite easy to create and use. Excel calls them Sparkline charts but I have seen them called micro-charts in other places. A Sparkline chart is basically a chart that fits within a single cell as shown below. They are quite easy to create as you can create one with three simple steps.
As you may already know, Excel will automatically fill a row or column with the names of months or weekdays by simply typing the first one or two and then using the drag handle. But what if you wanted to modify these values? What if you have a list of values you have to enter frequently? Excel allows you to not only edit the default custom lists, but allows you to create your own. To do so, simply click on File then Options. This will open a new window and then you should click on Advanced ( I told you this was going to cover advanced items.) Scroll to the very bottom, and look under the General heading. The last item contains a button labeled Edit Custom Lists. Click this button and a new window will open as shown below. Here, you can create a new list so that the next time you enter this list in a spreadsheet, you can simply enter the first value and let Excel fill out the rest for you.
I’ve worked on all types of spreadsheets from small single sheet to very large multi-sheet goliaths. When working on some of the larger ones, sometimes, there was a need to see how changes affected formulas located over a wide range of sheets. And then other times, I might have forgotten to check one of them after some formulas changed. Well, that all changed with the Watch Window. This is a very simple tool that allows you to monitor calculations from different points within the spreadsheet. Go to the Formulas ribbon and then look to the right, there is an icon for the Watch Window. Click on this and a new window will open as shown below. This window floats on top of Excel and allow you to add cells that you wish to watch. By using this it allows you to monitor key formulas throughout the spreadsheet as you make changes in other places.
Hopefully, I’ve dug deep enough to help expand your use and knowledge of Excel. This article contained some items for the more advanced user, but just as I stated in my first and second articles there are so many more tips and tricks out there. If you found this information useful or have any questions concerning Excel, let me know below or connect with me on Twitter, I’d love to chat!
Director of Magic
Come work with some truly magical people! We are hiring a Senior Infrastructure Engineer/Consultant - Fort Walton B… https://t.co/2BpxDVvCwH
Come work with some truly magical people! We are hiring a Senior Infrastructure Engineer/Consultant - Fort Walton B… https://t.co/sGN6FQoiEh