Excel tips&tricks
1 like497 views
This document provides an overview of spreadsheet tips and tricks including: 1) How to fill in series automatically by dragging the fill handle after entering the first two items. 2) How to perform date and time calculations like determining the number of days since a date or the time spent on a task. 3) How to create scenarios to model "what-if" situations and view a summary of scenarios. 4) How to create and manipulate pivot tables to dynamically summarize and view data in a spreadsheet.
More Related Content
What's hot (18)
Similar to Excel tips&tricks (20)
More from Joel May (20)
Recently uploaded (20)
Excel tips&tricks
- 1. Spreadsheet Tips and Tricks A presentation by Joel May I. Fill Series A. A series is any sequence of entries that form a natural list or pattern (e.g. days of the week, months of the year, series of numbers) 1. Move the cursor to Cell A1, and type “January” (no quotes) and press ENTER 2. We could now move to Cell A2 and type “February” then press the left arrow and, in Cell A3 type “March,” etc. but that is too much work. Instead 3. Be sure that the cell containing “January” (A1) is highlighted 4. Place the mouse pointer on the small black box at the lower right corner of the cursor (this is called the “fill handle”). The pointer will change to a black cross with the word “Fill” 5. Press down the left mouse button and drag the handle to the right to include Cells A2 and A3, then release the mouse button. 6. Voila! You have entered a series (of month names from January to March) 7. To get a whole year’s worth of month names, highlight the last of the cells containing month names (A3 which contains “March”) and drag the fill handle down to Cell A12 8. You can fill series either down through a column, or across through rows B. Other series available to you include days of the week (either Mon - Sun or Monday - Sunday), and number series (1,2,3,4,… or 10,9,8,7,…). 1. To enter a series of consecutive numbers, enter the first two numbers in the appropriate cells, then highlight BOTH cells and drag the fill handle of the second cell 2. If you wanted to enter a series such as 5, 10, 15, 20, … or 1,3,5,7,9,11, … you could enter the first two numbers (5 and 10 or 1 and 3) in adjacent cells, highlight both cells and drag the fill handle to create the desired series C. More complex series include any entry with an integer or an ordinal number 1. Period 1, Quarter 1, Item 1, etc. 2. 1st Period, 1st Quarter, 1st Item, etc. D. Other possibilities are suggested by a study of the Edit | Fill | Series dialog box E. To create a custom fill series 1. Choose Options from the Tools Menu and click on the Custom Lists tab. 2. Here you can either enter the list directly or create cell references to a list in a spreadsheet. 3. After the list is created, you can access it by entering the first name on the list and dragging the fill handle
- 2. II. Date and Time Arithmetic A. All Dates in Excel are recorded in the form of a number representing the number days that have elapsed since 1/1/1900 B. All Times in Excel are recorded in the form of a decimal fraction of a day beginning at 12:00 midnight C. To calculate the number of days since you were born 1. In cell A1, enter today’s date either by typing in mm/dd/yyyy or by entering the formula for today’s date which is =NOW() a) If you format the cell containing the formula =NOW() as a date, you’ll see today’s date b) If you format it as time, you’ll see current time 2. In cell A2, enter the date you were born by typing in mm/dd/yyyy 3. In cell A3, enter the formula =A1-A2, and format the cell as a number with 0 decimal places D. To display the number of months remaining in the current year 1. Enter the formula =12-MONTH(NOW()) a) NOW(), as we have seen, returns the current date b) MONTH(NOW()) gives the month of the current date as a number from 1 to 12 c) Subtracting this result from 12 gives the number of months remaining in the current year E. To calculate the time spent on a particular task measured in hours 1. In cell A1 enter the start time either in “military time” format (13:30) or with AM or PM indicators (1:30 PM) 2. In cell A2 enter the ending time 3. In cell A3 enter the formula =24*(A2-A1) and format the cell as a number with 2 decimal places III. Scenarios A. Create a worksheet like SCENARIO.XLS
- 3. B. Name the Cells 1. Highlight the blocks of cells 2. Choose Insert | Name | Create 3. Click on the Left Column radio button 4. Click OK 5. This names of each of the cells in the right hand column with the name in the corresponding cell in the left hand column C. Create a Scenario 1. Highlight cells C3 through C5 2. Choose Tools | Scenarios to display the Scenario Manager 3. Choose Add and give the scenario a name. Since this is the first scenario, call it “Original Data” 4. Verify that the Changing Cells area contains a reference to cells C3 through C6 5. Click on OK 6. In the Scenario Values dialog box, check that the following values appear in the list from top to bottom a) 150000 b) .065 c) 12 d) 25 7. Click on OK D. To Add Scenarios 1. Name the new scenario 2. Choose OK 3. Change the values in the Scenario Values dialog box 4. Click OK E. To View or Change Scenarios 1. From the Menu Bar a) Choose Tools | Scenarios b) Choose the scenario you want to view from the list c) Click on Show to view d) Click on Edit to change 2. From the Tool Bar a) Creating the Tool (needs to be down only once) (1) Right click on the Toolbar and choose Customize (2) Choose the Commands tab and choose Tools from the Categories List (3) Scroll down to locate the drop-down Scenario List (4) Drag and drop it on one of the active Tool Bars (5) Click on Close to close the Customize dialog box
- 4. b) Using the Tool (1) You can view any scenario by choosing its name from the drop-down list (2) You can add a new scenario directly to the list (a) Change the figures on the worksheet itself (b) Highlight the Changing Cells area (in our case cells C3 through C6) (c) Click in the drop-down scenarios list (d) Type the name for the new scenario and press Enter (e) The new scenario is added to the list F. To Summarize Scenarios 1. Choose Tools | Scenarios 2. Click on Summary 3. Choose the Scenario Summary option 4. Verify that the Result Cells area contains C8, C10 and C11 5. Click on OK 6. A new sheet named Scenario Summary will open in your workbook containing a formatted scenario summary that can be printed. 7. The + and – buttons at the far left and top of the worksheet can be used to collapse or expand the summary as desired IV. Pivot Tables A. Create a worksheet like PIVOT_TABLE.XLS B. Initiate the Pivot Table 1. Choose Data | Pivot Table and Pivot Chart Report 2. Answer the Questions in the Dialog Box a) Where is the data you want to analyze (1) In our case is in a Microsoft Excel list or database b) What kind of report do you want to create (1) Today we want a Pivot Table
- 5. 3. Click Next 4. Excel will attempt to identify the range of data to be used in the Pivot Table. Verify that the range is correct and click Next 5. Choose where to put the Pivot Table (usually in a New worksheet) 6. Click Finish 7. The empty Pivot Table will be displayed along with a dialog box which displays the heading of each of your columns of data C. Use the Pivot Table 1. To view expenditures by category for each month a) Drag the Month object from the dialog box to the Drop Column Fields Here area of the Pivot Table b) Drag the Category object from the dialog box to the Drop Row Fields Here area of the Pivot Table c) Drag the Amount object from the dialog box to the Drop Data Items Here area of the Pivot Table d) The Pivot Table results are displayed 2. To view expenditures by vendor for each month a) Drag the Category Object from the Pivot Table to the dialog box b) Drag the Vendor object from the dialog box to the Pivot Table c) The new Pivot Table results are displayed 3. To view expenditures by vendor and category for all months a) Drag the Month object to the Drop Page Fields Here area of the Pivot Table b) Drag the Categories Object from the dialog box to the Pivot Table c) A Pivot Table displaying expenditures by vendor and category for all months is displayed 4. To view expenditures by vendor and category for a particular month a) Click on the drop-down arrow next to the Month Object b) Choose the month you want to view V. Functions (see following pages)