Although, Halloween may be over and while Thanksgiving is around the corner, here is a little treat on Excel tricks for which to be thankful.
People are using Excel daily, but odds are they are not unlocking its full potential. This article will outline five tips and tricks using the example of a spreadsheet of donor data that can be transferable to assist with managing client information. The article features screenshots and examples in a sequential manner in order to ensure that you can to replicate the work within your own data.
These tricks are just a framework to get started- dive into how you can maximize your capabilities with Excel at DigitalC’s Data Analytics Workshops, as part of the Learning Studios, at our November Workshop held Nov. 14-16 HERE.
1.2.3. Let’s “Excel”:
1. Navigation Tricks
With especially large datasets, it can be a pain to select portions of the dataset by dragging the cursor. Excel allows you to avoid this through a couple of handy shortcuts.
With Ctrl + Arrow, you can navigate from your current cell to the last cell of data on your sheet, in the direction of the arrow that you press.
If you want to select data, simply press Ctrl + Shift + Arrow. After placing the cursor into the Name Cell, I’m able to select the entire Name column by pressing Ctrl + Shift + down arrow. At this point, If I want to select all the rows to the right, I press the right arrow, while still holding down Ctrl + Shift. To deselect the data, I simply press the left arrow and then the up arrow.
If I want to select all of the cells with data, I can press Ctrl + Shift + End and the cursor will select everything from the current cell to the lowest right-hand cell with data.
If I want to select all of the cells with data in the sheet, regardless of what cell I am currently in, I can press Ctrl + Shift + *.
I. Love. Macros. If you have ever opened Excel and found yourself pointing and clicking the same commands in succession, well, those days are over. Macros are simply sequences of commands, and they are an astoundingly simple way to automate routine tasks. You can create one by coding it with VBA or you can simply record yourself performing all the actions.
For example, suppose I have data in three tabs that I want to format in a similar way. Simply go to the View tab, click on Macros, and select New Macro. Type in a name for the Macro. Optionally, select a shortcut key that you can use to run the function, and type a description.
Now to record the macro, simply go to Macros and hit Record Macro. Now, format your table, and then hit stop recording.
Now, I can apply my macro to my other tabs by simply clicking over to them and hitting Ctrl + Shift + A, the shortcut that I used to save my macro.
Voila! This was a simple example that saved a few seconds of time, but macros can be used to automate repetitive tasks that are much more time-consuming, like creating tables/charts/worksheets, organizing data, connecting to data sources, and more.
This is one of Excel’s most powerful features, and it makes it possible for you to look up values in a table based on information in other rows/columns.
Index match is actually a combination of two formulas: Index, and Match.
The Index formula allows you to find the contents of a cell if you specify the range and row_or_column. The Match formula allows you to find the position of a cell within an array by matching against certain criteria.
For example, in our table, if I wanted to know who was the fifth donor listed, I could use Index and it would return “Roland Fryer”. If I wanted to find the position of the cell containing the name “Roland Fryer”, I could use the Match formula, input the name I’m looking for, the column that I want Excel to look in, and 0 to indicate that I’m looking for an exact match, and Excel would tell me that it is in the fifth cell.
Given this, what if I wanted to find out the value of the last gift that Roland Fryer donated? Well, I would use the Match formula to find the position of the cell that contains the name Roland Fryer. Now that I know the position, I would then feed that result into the Index formula to find the output in the Last Gift column. This would return to me the amount of the Last Gift given by Roland Fryer.
If you’re wondering why to use Index Match instead of VLOOKUP or HLOOKUP - Index Match provides the same functionality of these two formulas, but you only have to remember one formula. Additionally, you don’t have to count to figure out which column or row you want to reference, which means that you can insert columns and rows in the future without messing up the results of Index Match. Check out Index Match and see how it can help you!
Learn how you can maximize your capabilities with Excel at DigitalC’s Data Analytics Workshops, as part of the Learning Studios, at our November Workshop held Nov. 14-16 HERE.