Tag Archives: Excel 2010

Connecting Data Sets using VLOOKUP in Excel

In my dream world, the data management systems I use would allow me to extract data in the exact sequence and combinations I need. More often, I am able to extract only certain segments of data and then have to build bridges to connect those data sets outside of the original source.

Within the same or multiple workbooks, different data sets may be brought together using the VLOOKUP function. In this example, we reminisce to the lazy, hazy days of attending summer camp as a kid. Campers Becky, Joel, Vicky, Julia, and Anthony have signed up for various activities each day of the week. Their choices have been entered in the data management system as Activity Codes and are listed on the spreadsheet named Campers.

VLOOKUP_Excel_1

VLOOKUP_Excel_3In order to create a schedule for each camper for the week, the translation of the codes to the Activity name and Location will be accomplished using VLOOKUP. In a second sheet named Activities, the data is setup such that the Activity Code is in the first column of the data range. The Activity Code fulfills the first requirement in using VLOOKUP in that this is the common ID or key that exists in the each dataset. The second requirement is that the Lookup table is sorted by this ID or key.

VLOOKUP_Excel_2

To connect these two sets of data, the VLOOKUP formula will be entered in the Campers spreadsheet cell D2.

 

VLOOKUP_Excel_4

The first parameter needed to identify Becky’s Monday Activity is the Activity Code found in cell C2.

The next parameter is the address of the data containing the name and location of the date — the absolute reference Activities!$A$1:$D$6.

The next value is the column number of our target data, Activity in the 2nd column.

The last value in the VLOOKUP syntax will be FALSE. FALSE is used when an exact match is required. The alternative TRUE would be used if an approximate match was desired. I use FALSE almost 100% of the time. In fact, I’m not sure if I’ve ever wanted an approximate match and chose TRUE.

Using almost all of the same values, the Location may be filled by changing only the third parameter in the VLOOKUP formula. In cell E2 on the Campers sheet, we’ll type =VLOOKUP(C2, Activities!$A$1:$D$6,4,False). This will lookup on the same row of code AL001 in the Activities spreadsheet but will return the value in column 4 — Pool 1.

VLOOKUP_Excel_5

Copying the formulas to connect the data sets for all remaining rows will enable schedules to be prepared for all campers. Let the fun begin!

VLOOKUP_Excel_6

If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.

 

A Quicker Way to Move or Copy Spreadsheets in Excel

Moving or copying spreadsheets to another location is a straightforward process from the Cells group on the Home menu tab. A quicker way is to drag and drop to the new location.

Move or Copy using Cells Group

Let’s say we have a workbook which captures data for multiple years with one sheet providing summary information. The individual spreadsheets are named as follows:Move_Copy_Sheets_Excel_1 We would like to add data for 2013 and move Summary as the first spreadsheet. Select the Summary sheet. From the Home menu tab, click on the Format down arrow in the Cells group.

Move_Copy_Sheets_Excel_2

The dialog box launched will allow a different workbook to be chosen. In this case, we leave the default as the existing, “Revenue Forecasts.” We want to move Summary before the first sheet which is 2010. After selecting 2010, click OK. Because we are moving, not copying, the Create a Copy checkbox is unchecked.

Move_Copy_Sheets_Excel_3

Next, we’ll create a copy of the 2012 sheet and rename it 2013. Select Format from the Cells group and select Move or Copy Sheet. The book remains “Revenue Forecast,” we click on Sheet5, check the Create a copy checkbox, and click OK.

Move_Copy_Sheets_Excel_4

Clicking back on Format in the Cell group, this time, we select Rename Sheet. The 2012 (2) is highlighted and typing “2013” completes our task.

Move_Copy_Sheets_Excel_5

The Quicker Way — Move or Copy using Drag and Drop

(Too quick even for screenshots!)

1.  Click and hold the Summary tab, drag to the left of the 2010 tab and release.

2. Hold down the CTRL key, click and hold the 2012 tab, drag to the right one position and release.

3. Right click the 2012 (2) tab, click on Rename Sheet, type 2013, and press Enter.

DONE!

Move or Copy Between Workbooks

Both methods work equally well across two open workbooks.  When using the Cell Group method, the Move or Copy dialog box will allow the second  workbook to be chosen and then the location and the move or copy choices may be made just like in a single workbook.

Move_Copy_Sheets_Excel_6

To drag and drop, only one extra step is needed for a second workbook. Once both workbooks are open, select View Side by Side in the View tab. If only two workbooks are open, they will appear side by side. If more than two workbooks are open, a dialog box will open in order to select the workbook to show next to the original.

Move_Copy_Sheets_Excel_7

Move_Copy_Sheets_Excel_8

Now that the two workbooks are side by side (or top and bottom), the original spreadsheet may be dragged to move to the other workbook or CTRL dragged to copy.

Shrinking a Spreadsheet to Fit on One Page in Excel

Fit_One_Page_Excel_a
Photo: David Castor via Wikimedia Commons

Ever wish you could shrink your car when faced with parallel parking in a really tight space? That’s how I feel when a spreadsheet is just slightly bigger than will fit on one page. The usual solutions are choosing larger paper (e.g., 11″ x 17″), making the page margins smaller or shrinking the size of the columns or rows. That can be a lot of trial and error, making a change, checking Page Preview, tweaking a little more, checking again (not unlike inching the car forward, reverse, forward, reverse).

The faster solution is to use Scaling in the Print menu.

1. Click File on the Menu bar, select Print, and then Scaling under Settings. The default is No Scaling.

Fit_One_Page_Excel_1

2. Click on the dialog launcher (small down arrow) and 3 other options appear.

Fit_One_Page_Excel_2

Fit Sheet on One Page will take all of the selected print area or the whole sheet and shrink everything horizontally and vertically to fit on one page.

Fit All Columns on One Page will shrink the selection horizontally so that all columns fit on a page, but rows will print on as many pages as needed.

Fit All Rows on One Page will shrink the selection vertically so that all rows fit on one page but if the columns do not fit on one page, they will flow to additional pages.

The Print Preview should adjust as you choose different options.

How to Create Fill-in Forms using Excel

On a recent shopping trip to purchase a wedding present, I came to a standstill in the kitchen appliance aisle. I was stunned by the number of “one trick ponies” beckoning for my attention – waffle makers, Mickey Mouse shaped waffle makers, rice cookers, bread makers, silver dollar pancake makers, panini makers, sandwich grills. I wondered if it wouldn’t be more space and cost efficient to just use a good set of pots and pans.

So it is with software designed to do a singular task. I am sometimes infatuated by the latest marketing claims that promise to make quick order of a particular function. With a little more thought, however, I am usually swayed back to my tried and true tools. One example is making fill-in forms, several software packages may be purchased for this task. However, creating fill-in forms in Excel takes just a few steps.

There are two settings that must be coordinated, both found in the Format dropdown in the Cells group of the Ribbon. The default settings for the two settings are set as Protect Sheet set to OFF and Lock Cells set to ON. We’ll double check the Lock Cells as a first step.

1. Press CTRL-A to select the whole worksheet. Click on the drop-down arrow on Format in the Cells group. Check that the Lock Cells is ON, indicated by the shaded square around the lock icon. If in doubt, click the lock and the shaded background will toggle on and off.

Forms_Excel_1

2. Create the form with appropriate labels and open cells in which the users will input their information. For example, Cell A2 below shows the label “Name.” Cells B2 and C2 are blank and Merged as one cell for entry of the user’s name.

Forms_Excel_3

3. Select each of the input cells, in the above case, B2, C2, E2, and B3, C3, D3, E3. Cells which are non-contiguous may be selected by holding down the CTRL key while clicking on each cell.

4. Click on the drop-down arrow on Format in the Cells group. In the Protection section, click on Lock Cell. This will toggle OFF the lock for only the selected cells.

Forms_Excel_5

5. Click Protect Sheet right above the Lock Cell option. The dialog box provides several options to enable a customized user experience. Protecting the spreadsheet with a password will prevent the user from making any changes to the form without entering the password. Checking the box “Select unlocked cells” and unchecking “Select locked cells” will allow the user to conveniently tab through all of the input cells and skip over the label cells. Forms_Excel_6

6. Finally, save the file as an Excel Template so that each time the file is opened, a new copy is opened, leaving the original intact for future uses.

To see this form in action, download it here: Fill-in Form in Excel

How to Remove Auto-Formatting of Hyperlinks

You know that feeling when something is just a little bit bothersome but not quite annoying enough to spend time to resolve? When I have one of those, the annoyance simmers below the surface for a really long time and then one day I get the sudden urge to fix it. As in, fix it right now, I can’t stand it another minute! After finding the solution, there is such joy that I wonder, “Why did I wait so long?”

So, without building up any more suspense, today’s “I can’t stand it another minute!” was the default AutoCorrect which replaces Internet and network paths with hyperlinks. Email addresses convert to a hyperlink, such as info@thespreadsheetwizard.com. I don’t like this as I am not usually emailing directly from the Excel spreadsheet and if the list is printed, the hyperlinks appear in a different color and underlined. I used to go back and remove the hyperlink manually by right-clicking and selecting “Remove Hyperlink.” Really not a big deal, until the day it suddenly becomes an annoying big deal!

Here’s how to change this default setting:

  1. Select File from Menu Bar. Then select Options almost at the bottom of the list.

How to Remove Auto-formatting of Hyperlinks Step 1

2. Select Proofing and then click the AutoCorrect Options button.

How to Remove Auto-formatting of Hyperlinks Step 2

3. Select AutoFormat As You Type tab. Then uncheck the Internet and network paths with hyperlinks box.

How to Remove Auto-Formatting of Hyperlinks Step 3

 

4. Click OK button. Click second OK button.

That’s it! Just a few clicks and I’m doing the happy dance! This reminded me of how much customization is available throughout the program and how just a few minutes of time can streamline my usual tasks or eliminate default settings that don’t suit me.