Excel Tips and Tricks Part 3: Now it’s Time to have FUN

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.
 

Indirection

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.

=INDIRECT(ref_text, [a1])

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.
 

Charts

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.
 

1. First, and obviously you need your data. For this example I am going to have two columns of data. Column A contains the Axis value and Column B contains the data values.
2. Let’s go ahead and add a scroll bar to our sheet.
   1. Go to the Developer ribbon.
      1. If you cannot find this ribbon that’s OK.
      2. Go to File and then click on Options.
      3. Now click on Customize Ribbon.
      4. In the section on the right find Developer and check it.
      5. You should now have the Developer Ribbon.
   2. Under the Controls section click on Insert.
   3. Locate the scrollbar and click on the icon and draw your scroll bar on the sheet.
   4. Next format the control and go to the Control tab.
   5. On the control tab enter a value for the Cell link option.
      1. It doesn’t matter exactly where this is but it will be used in the next main step.
      2. If you do not know what this does it will give us the current position of the scrollbar and display that value in the cell you entered during this step.
   6. On the control we need to set the Maximum value.
      1. This can sometimes need a little tweaking later, but I usually start out at the count of data rows minus the number of columns you are going to display on the chart at a given time.
      2. If this isn’t set up properly you can get an error in the formulas.
3. Next we need to set up a couple of specific formulas.
   1. I typically place these immediately next to the cell I entered as the Cell Link value above.
   2. In the first cell you need to enter the Maximum value you set within the control.
   3. In the second cell you need to enter the following formula.
      1. =(Maximum Value entered above – Cell Link) + 1
      2. What we are doing here is reversing the value of the scrollbar. If you do not do this, the chart will scroll backwards from the expected direction. So if you were to move the scrollbar to the right the chart would scroll the data to the oldest. 
4. Next you need to set up two named ranges. One for the axis value and one for the data values. I named them DataLabels and Data.
   1. The first you need to create is for the data labels. Below is the formula for the downloadable sample file.
      1. =OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$40)+Sheet1!$D$2*-1,0,-12,1)
         1. I want to point out a couple of specific areas within this formula.
         2. The first is the reference to cell D2. This should be the cell you entered in the second formula from step 3 above.
         3. The next value is the -12 value I have entered. This represents the number of rows I will be displaying data for within the chart.
   2. The second formula will select the actual data. Below is the formula.
      1. =OFFSET(DataLabels,0,1)
      1. 1. 1. The main item I want to point out is the named range value. This should be the name you entered for the first named range you created above.
5. Now the next step is to create the chart and set the data sources appropriately.
   1. Simply add a chart to your sheet.
   2. Right click and select Select Data.
   3. In the new window under the Legend Entries section click Add.
      1. Under Series values enter =SpreadsheetName.xlsx!NameRange.
      2. In my sample file this is =ScrollingChart.xlsx!Data.
   4. In the Axis Labels click Edit.
      1. Under Axis label range enter =SpreadsheetName.xlsx!NameRange.
      2. In my sample file this is =ScrollingChart.xlsx!DataLabels.
6. You are now finished. As you move the scrollbar the data in the chart should scroll in sync with it.

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.
 

Data Validation

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.
 

• On the Settings tab: Decide what type of data should be entered.
• On the Input Message tab: You can alert the user of what data should be entered when they click on any of the cells.
• On the Error Alert tab: You can decide what action should occur if the user enters data outside the parameters you entered on the Settings tab.
  • You can choose to stop the user and not allow the data to be entered.
  • You can warn the user, but allow the data to be entered.
  • You can simply inform the user and allow the data to be entered.

Sparkline???

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.

1. Select the data for the Sparkline you wish to create.
2. On the Insert ribbon locate the Sparkline section and select the type of Sparkline you want.
   1. You have three options to choose from.
      1. Line
      2. Column
      3. Win-Loss
3. Specify the target cell where you want the Sparkline to be displayed.
4. Optionally you can format the Sparkline to look as your needs require.

 

 

Custom Lists

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.
 

Watch it All

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.


 

Conclusion

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!