CoachEel3085
Lab Exercise 5 Excel Fundamentals, Part 1 Excel is a highly…
Lab Exercise 5 Excel Fundamentals, Part 1
Excel is a highly versatile spreadsheet application. Excel proficiency is a fundamental and valuable skill for any business major. If you are new to Excel, pay close attention to the first two lab exercises. They introduce some basic but very helpful techniques that will help you in your career. Knowing them, you will be able to demonstrate proficiency by completing tasks quickly. On the other hand, every work group seems to have a story about a new hire (long gone) who had no idea how to use Excel and took hours or even days to achieve what a proficient Excel user could have done in a few minutes.
Find the Excel application on your computer, open it, and make a blank workbook. Each Excel file consists of a “workbook” composed of one or more pages called “worksheets”. Typically, users separate data (like sales data from different products or time periods) by putting it on separate worksheets. You can see that the first worksheet is a grid of rows and columns. Rows are assigned numbers and columns are assigned letters. The intersection of each row and column is called a cell and referenced by its column and row. For example, the cell A1 in the upper left is at the intersection of column A and row 1.
In Excel, cells can hold:
Numbers: numeric values
Text: non-numeric text like column labels
Formulas: assigning values, typically derived from other data in the workbook, like the sum of all values in a column
Functions: special formulas not easily created by users, like statistical operations or extracting specific portions of text from another cell
Selecting columns, rows, & cells:
To change the data in a cell, you first need to select it. By default, a new worksheet begins with cell A1 selected. You can select a different cell just by clicking on it. To select an entire column or row, click on the corresponding letter or number. To select a range of cells (a contiguous, rectangular group of cells), you can click on one corner of the rectangle, then hold the left mouse button down and sweep across the rest. And, to select a non-contiguous group of cells, just keep the Ctrl key pressed while you select each cell.
Action 1: Select the cell range A1:G8 (ranges are given as the cells at the upper left and bottom right corners of the rectangle).
Shortcuts:
Excel provides many keyboard shortcuts for common actions, like Ctrl-X for Cut, Ctrl-C for Copy, Ctrl-V for Paste, Ctrl-Z for Undo, and Ctrl-Y for Redo.
Action 2: Enter the text “abc” in cell C5 and press Enter. Select C5 again and press Ctrl-B and Ctrl-I to change the text style to bold and italics.
Action 3: Select cell C5. Press Ctrl-C. Notice that the border around cell C5 is moving – this is a reminder that C5 is the one you have selected to copy. Select cell E7, then press Ctrl-V. Notice that the border is still moving. That’s because you can copy to additional cells. Select F9 and press Ctrl-V to copy from C5 again. To unselect C5 without clicking it, press Esc.
Action 4: Select cell E7. Press Ctrl-X. Notice that the border around E7 is moving. Select D2 and press Ctrl-V. Notice that the cell E7 clears and its border returns to normal, since we’re cutting instead of copying.
Action 5: Press Ctrl-A (for select all). Then press Delete to clear the entire worksheet.
When practical, keyboard shortcuts are designed to let your hands complete tasks separately. This saves time: one hand can use the mouse or the numeric keypad while the other operates the main keyboard area. If your job requires you to use Excel much of the day, the time savings can be substantial. For example, the shortcuts for copy (Ctrl-C), cut (Ctrl-X), and paste (Ctrl-V) can all be done with your left hand, while your right hand uses the mouse or arrow keys to navigate the worksheet.
Switching selections:
To change the selected cell, you can of course use the mouse. But if you’re typing in a lot of data, there are keyboard shortcuts that are much faster. The simplest is using the arrow keys. If your data is arranged in sequential columns in a row, though, you can use Tab and Shift-Tab to move to the next or previous column, respectively. Also, if you’ve filled out a row, you can press Enter to select the first cell on the next row. If your data is all numbers, your right hand can type on the numeric keypad while your left hand controls which cell is selected. This feature automatically follows your actions – if you’ve entered data in row 1, columns D through G, pressing Enter will select D2.
Action 6: Select cell A1. Using the Tab and Enter keys, enter the following values:
A1: Last Name; B1: First Name; C1: Sales, D1: Zip Code
A2: Smith; B2: Jack; C2: 100,000; D2: 60614
A3: Carlson; B3: Sarah; C3: 150,000; D3: 60201
A4: Bird; B4: Peter; C4: 200,000; D4: 60657
A5: Schwart; B5: Melissa; C5: 250,000; 60614
Block Selection:
Suppose you want to select the range A1:D5, for the table you just created. Since the area is small, it can easily be selected with the mouse. However, suppose the range was much larger, with many thousands of rows and columns – using the mouse would be slow and tedious. Therefore, Excel lets you select continuous blocks of data using the arrow keys.
Action 7: Select cell A2. Then, holding down the Shift and Ctrl keys, press the down arrow key. The range of selected cells should expand to include A2:A5. If you press Ctrl-Shift-[right arrow], the range will then include A2:D5. You could also click on D5 and select A1:D5 by pressing Ctrl-Shift-[up arrow] and then Ctrl-Shift-[left arrow]. This range selection shortcut works best with continuous cell ranges – any gaps in the data range will require additional keypresses. Enter numbers in A7, A8, and A10 and then select A2. You will need to hit Ctrl-Shift-[down arrow] four times to select the entire range A2:A10.
Inserting/Deleting Columns and Rows:
Sometimes, you’ll want to insert or delete columns or rows without changing the rest of the worksheet. This can be done by right-clicking on the column letter or row number and then selecting the insert or delete option. But, perhaps you’re using a laptop where the right-click is awkwardly accessed through a touch screen; then, you might use a keyboard shortcut.
Action 8: Click on the letter for column C. To insert a column to the left of column C, press Alt+I (for insert), then C (for column). Then press Ctrl+Z for Undo. To insert a row above row 5, first click on the 5. Then, press Alt+I, then R (for row). Let’s delete this row instead of undoing it. Since the new row is already selected, we can immediately press Alt+E (for edit), then D (for delete).
Autofill:
Autofill is a simple technique that lets you quickly fill a series of cells with a pattern. Open a new worksheet by pressing the “+” symbol in the lower left corner of the window. Suppose you need to fill cells A2:A501 with the numbers from 1 to 500. You could complete this by typing in all the numbers but there’s a better way: type in a 1 in A2 and a 2 in A3 and then use autofill. The two numbers establish a pattern of every row down increasing the number by 1. Select both cells A2 and A3. In the lower right-hand corner of A3’s border, you should see a small black square. This is the fill handle. When you hover the cursor over the fill handle, the cursor will change to a small black cross. If you click and drag the fill handle to cell A501, you will fill the range A2:501 with the series of numbers.
Autofill works well when a constant value is added to each cell in a series. For example, if you type 2 in B2 and 4 in B3, autofill will count by twos. However, in more complicated series, it can give strange results. Try typing 2 in C2, 4 in C3, and 8 in C4, then using autofill. Also, if Excel cannot determine a pattern from a range of cells, autofill will repeat the sequence. Type the numbers 3, 6, and 4 in E2, E3, and E4, respectively. Then select E2:E4 and “=2+0.5” in E3, then use autofill and see what happens. So, while autofill is useful, it won’t recognize every pattern.
Besides simple numbers, Autofill can extend patterns of dates or times, such as Q1 2018, Q2 2018, and so on – try autofill before typing a long list of times or dates! Because names of days and months are often misspelled, Excel also recognizes their abbreviations. So, you could enter January in G2 and February in G3 and use autofill to extend that pattern through G25 for two years’ worth of months. You could also enter Jan in H2 and Feb in H3 and use autofill over H2:H13. The same approach will also work with days of the week, although of course the pattern repeats every seven days instead of twelve months. However, you should avoid mixing abbreviations with the full words. Try typing January in J2 and Feb in J3, then using autofill.
