Welcome to Part 2 of my 3 part series on using Excel. If you’re starting here you either decided to skip the first article, as it may have not been advanced enough, or this is the first one you found. In that case you should know that Part 1 was created to give beginner users of Excel some tips to help them get started.
In this article, I plan to dig a little deeper for those of you who have some experience with Excel. In the last and final article of this series, I plan to dig even deeper to give tips and explain how to perform a bit more advanced processes within Excel.Let get started.
VLOOKUP()
If there is a single formula within Excel that I have used more than any other it would be VLOOKUP(). If you are not familiar with this formula yet, please pay attention now. In the first part of this series, I mentioned that in a past life, I was a data analyst, and in my first week on the job, I was given the task of combining data from two different spreadsheets into a single spreadsheet.
Spreadsheet 1 contained a master store list with information pertaining to each store. Spreadsheet 2 contained all of the revenue data from the stores. Looking up each store and copying over its data one at a time simply wasn’t going to cut it. So I learned about the VLOOKUP() formula very fast. This formula performs a vertical lookup and all you need is a single column of data to join or lookup that is common in both sets of data. In my case, it was the store number. Below is the syntax for this formula.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let me breakdown this formula for you.
- lookup_value – This is a required value and will be the value we are searching for to make the join between the different sets of data.
- table_array – This is required and represents the array of cells that contains the data we are searching through.
- col_index_num – This is required and represents the column in the table array we wish to bring back as the result of the formula. Excel is 1 based so the first column in the table array is 1 not 0.
- range_lookup – This is the only optional value in the formula, but I consider it required. This is used to determine if you want to look for exact matches or possible/approximate matches.
**Please pay attention to this optional value. Even though it is optional, I always recommend providing a value. If you leave this value out of the formula, Excel will bring back possible or approximate values as well. So if you are working with exact data, that can cause quite a few issues since the data will be incorrect. I always use ‘false’ for this value except for very special cases. By sending false you are stating you want an exact match and if one cannot be found it will not return any data but rather #N/A as the result.
SUMIF()
Another formula I found to be quite useful was SUMIF(). This formula allows you to sum totals based on specific criteria. Below is the syntax for this formula.
=SUMIF(range, criteria, [sum_range])
Let me break this formula down for you.
- range – This is required and is the range of cells you will be searching through.
- criteria - This is required and is the value you are searching for in the range value.
- sum_range – This value is optional, but I find that in most cases it is required to get the correct results back. If you leave this blank the formula will use the range to perform the sum, but if the data you wish to sum is in another column you would enter that range here.
There are a couple of other formulas that I also use that are very similar to SUMIF().
These are:
- SUMIFS() – This allows you to use multiple sets of criteria and not just a single one.
- COUNTIF() – This performs a count in lieu of a sum based on a single criteria.
- COUNTIFS() – This allows you to use multiple sets of criteria and not just a single one.
Shade Alternate Rows
When you create a report or spreadsheet that is going to be presented or sent out, you want make it appealing and easy to view. An easy approach to this within Excel is to use the ‘Format as Table’ option on the Home ribbon. When you click on this option, a drop down menu will appear as shown below that give you a visual picture of what the different styles there are by default. To apply a style, simply select the cell range and then click on the image.
Custom Drop-Down Lists
If you are creating a spreadsheet that will be filled in by other users, you will want to ensure that data is inserted in a uniform manner. Take for example, a field where you require the user to enter the month of the year. Some may enter the full name where others may abbreviate; and it is important to remember that you are not protected from misspellings either. In these cases you could take advantage of creating custom drop-down lists. Not only does this assure that the data being entered is uniform, but it will also give your spreadsheet a more professional feel.
To achieve this, you first need to enter the values that will available in the drop down list. These values can be anywhere, but I recommend placing all of this data on a separate sheet and then hide that sheet before sending out the spreadsheet. So in my example you would enter the months as you would like them to be entered in the final cell. Then select the cell where the drop down list should appear. Now go to the Data ribbon and click on the ‘Data Validation’ option. This will open a new window where you can pick List from the Allow options and in the Source field enter the range of cells containing the month values and then click OK. Now when the user clicks in the cell, a drop down list will appear with these values to choose from, therefore, keeping the values entered uniform and correct.
Your Friend, the END Key
When you are working in a spreadsheet with large amounts of data and wish to move to the last column or row, you have a couple of options. You can scroll using the mouse, you can scroll using the scroll bars, or you can use the END key. I prefer the END key to navigate within the data. This will allow you to move from one end of the data to the other with two button clicks. Simply press the END button and then the arrow key of the direction you wish to move.
The Function Keys
One often overlooked area within Excel is the Function keys. Most everyone knows that within an Office product, and most other products, if you press F1 you will open the help window. But what about the other 11 functions keys? Every single function key has multiple options. Below is a chart with all of the function keys abilities as described by Microsoft. Look these over and use these to your advantage.
|
KEY
|
DESCRIPTION
|
|
F1
|
Displays the Help task pane.
CTRL+F1 closes and reopens the current task pane.
ALT+F1 creates a chart of the data in the current range.
ALT+SHIFT+F1 inserts a new worksheet.
|
|
F2
|
Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.
SHIFT+F2 edits a cell comment.
|
|
F3
|
Pastes a defined name into a formula.
SHIFT+F3 displays the Insert Function dialog box.
|
|
F4
|
Repeats the last command or action, if possible.
CTRL+F4 closes the selected workbook window.
|
|
F5
|
Displays the Go To dialog box.
CTRL+F5 restores the window size of the selected workbook window.
|
|
F6
|
Switches to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6 switches to the previous pane in a worksheet that has been split.
CTRL+F6 switches to the next workbook window when more than one workbook window is open.
NOTE When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.
|
|
F7
|
Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ESC.
|
|
F8
|
Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection.
SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys.
CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.
|
|
F9
|
Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
CTRL+F9 minimizes a workbook window to an icon.
|
|
F10
|
Selects the menu bar or closes an open menu and submenu at the same time.
SHIFT+F10 displays the shortcut menu for a selected item.
ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.
CTRL+F10 maximizes or restores the selected workbook window.
|
|
F11
|
Creates a chart of the data in the current range.
SHIFT+F11 inserts a new worksheet.
ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code.
|
|
F12
|
Displays the Save As dialog box.
|
$$$ (I’m not Talking about Money Here)
I want to take some extra time and focus on a specific function key. It’s one that I use just about every time I write a calculation or formula. What is this magical key? It’s the F4 function key. If you notice in the description, it simply states, “Repeats the last command or action, if possible.” To be honest, I don’t really know what that means and have never really understood it. What I do know is, within a formula, it will quickly allow you to change relative cell references to absolute references. For those who are not aware, let me explain in a little more detail.
Let’s say you write a formula like, =SUM(A1:A4), and you enter this formula in cell A5. Now, if you were to copy this formula in cell C5, Excel would dynamically change the formula to read =SUM(C1:C4) for you. This is a powerful feature within Excel; it is updating the formula to hopefully suit what you are asking it to do. But take for example, a VLOOKUP() formula where you do not want the lookup value column reference to never change, but only the row number portion. This is where the $ (dollar sign) comes into play. So in the formula =VLOOKUP($A4,Sheet1!$A:$K,11,0) I’m letting Excel know I don’t want it to update the column portion of the lookup value, but go ahead and change the row portion as I copy this formula around.
Also, notice I’m letting Excel know to never change the data array, which is my second value. This is important to realize because if I did not do this as I copied the formula around, it would change the data array and what it references, so my lookup column and column number index will be off and return bad results. For years, I would manually go in and place the cursor in the correct places and type a $ sign. By using the F4 function key, you simply place the cursor anywhere in the cell reference and press the key. It will cycle through each possible combination on each key press.
Named Ranges
There are times when my spreadsheets are quite large and have multiple sheets within them, performing calculations and pulling data from multiple sheets at the same time. To make my life easier, I use Named Ranges. This is just a more user friendly way to refer to a set of cells or just a specific cell. Under the Formulas ribbon, there is a Defined Names section.
Click on Define Name and a new window will appear as shown below. Simply give it a name you will remember and the cell, or range of cells it refers to, and you are good. The scope value depends on where you want the name accessible from. By default, it is set to the entire workbook, but you can change this to be only a specific sheet if you like. Then click OK and you are done. Now when you are adding a formula, you can type in the name you created and Excel will display it dynamically as you type also shown below.
Camera
With all the power that Excel has, there is still one thing it cannot do: Print areas from multiple sheets on the same page. Well, there is a work around to this, kinda. Within Excel, there is a Camera action that you can use to grab an image based on the currently selected cells. This is not something that is in any Ribbon, so you have to add is to the Quick Action Toolbar. This toolbar is located between the formula bar and the ribbon. To add the Camera icon, simply click on the down arrow to the far right and select on More Commands at the bottom of the drop down. In the new window under the Choose commands from, drop down select Commands Not in the Ribbon. Then, locate the Camera command and add it to the toolbar. To use this feature, you select the cells you wish to get an image of, and click the icon in the tool bar. Then select the area you wish it to paste this new image to. Now do the same for any other sheets you wish and place all of the images on a single sheet then print it.
Now, you could say I can just use a tool like Snagit to do a screen capture and achieve the same thing. And you could, but there is a really cool feature about using this Camera tool. The image is linked to the original data. So if you make any changes to the original data these images will be updated as well. So, you could make a master sheet of these images for meeting updates and each week as you update the main data the images will be updated as well and you just have to re-print them.
Conclusion
Hopefully, I’m starting to dig a little deeper and give you some new and useful knowledge to help while you are using Excel. This article contained some items for the more experienced user, but just as my first article was for beginners, there are so many more as there have been books upon books written on the subject. If after this you are still looking to go just a little deeper or find help with some of the more advanced options within Excel; you may enjoy the
next and
previous article in this hit series (links at the bottom of this blog). 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!