- Lecture Notes For Module 03 Working With Large Worksheets Charting And What If Analysis Manola Department Stores Inc 1 (45.95 KiB) Viewed 61 times
- Lecture Notes For Module 03 Working With Large Worksheets Charting And What If Analysis Manola Department Stores Inc 2 (33.34 KiB) Viewed 61 times
- Lecture Notes For Module 03 Working With Large Worksheets Charting And What If Analysis Manola Department Stores Inc 3 (53.65 KiB) Viewed 61 times
- Lecture Notes For Module 03 Working With Large Worksheets Charting And What If Analysis Manola Department Stores Inc 4 (38.72 KiB) Viewed 61 times
- Lecture Notes For Module 03 Working With Large Worksheets Charting And What If Analysis Manola Department Stores Inc 5 (54 KiB) Viewed 61 times
- Lecture Notes For Module 03 Working With Large Worksheets Charting And What If Analysis Manola Department Stores Inc 6 (36.69 KiB) Viewed 61 times
- Lecture Notes For Module 03 Working With Large Worksheets Charting And What If Analysis Manola Department Stores Inc 7 (35.76 KiB) Viewed 61 times
Read through requirements document on pp. EX3-1-EX3-4 Enter the title and subtitle and apply the Slice theme .Rotate text in a cell . Type the month in the cell indicated In the Home tab, locate the Alignment section and click the dialog box launcher Type in 60 degrees or click on the 75-degree mark; OK • Create a series of month names Fill the cells across-what happens? Excel recognizes that you have typed a month and assumes that you will be incrementing month to month and fills out the cells accordingly Even when Total and Chart are typed in the adjacent cells, the formatting is the same as the cells with the months o Click the Auto Fill Options button below the lower-right corner of the fill area to display the menu (read over the auto fill options explanation on p. EX3-8) Note the options for the Fill Handle on p. EX 3-8-depending on the increment required, the program will recognize and copy accordingly but you will need to copy both cells and then fill to get the desired pattern. Such as January, March (skipping a month), then the cells containing January and March should be selected and then filled across
• . . Increase column widths and enter data as instructed o Enter Row Titles Copy, paste, insert, and delete cells Type "What-If Assumptions" in cell A18 Select, copy, and paste cells A9:A13 into A19:A23 Read over Table 3-4 Press the Escape key to remove the marquee from the copy area Cells can be moved or copied by dragging and dropping the entries- be sure there is a block arrow and then drag the entries to the destination area for a "move." To "copy" to another location, hold down the CTRL key until a small cross appears next to the block arrow and drag to your destination SAVE as SC_EX_3_Manola Easy to insert rows (follow directions on pp. EX3-14-EX3-15); blank row will be inserted above the one you have selected o o Easy to insert columns (follow directions on pp. EX3-15-EX3-16); blank column will be inserted to the right of the column selected SAVE workbook Enter projected monthly sales Note the meaning of the error #REF! Format numbers using format symbols o Self-explanatory! Enter and format the system date o O Select H1; click Insert Function button in the formula bar; select a category; date and time; scroll down and click NOW Right-click the cell; format cells; number tab; date and time; change to date format indicated Use absolute cell references in a formula o What is the difference between a relative cell reference and an absolute cell reference?-the most important difference is that relative cell references in formulas adjust when the formula is filled or copied to other cells; absolute cell references in formulas stay constant they do not adjust when the formula is copied or filled to other locations To achieve absolute cell reference, press the F4 key after pointing to
BALSAR pattorio To achieve absolute cell reference, press the F4 key after pointing to the cell or typing it in the formula. This places dollar signs around the cell reference o . Use the IF function to perform a logical test . Another type of cell referencing is called "mixed" cell reference. This is explained in more detail on the bottom of p.EX 3-20 The If function in the tutorial is translated as: If the Revenue exceeds the amount in B21 (Sales Revenue for Bonus), then a bonus is entered in B9; if Revenue does not exceed the amount in B21, then there is zero bonus entered in B9 The If function can be accomplished by keying in the formula or using the Insert Function in the Formula bar Nested forms of the IF Function are explained on p. EX3-29 Adding and Formatting Sparkline Charts Click on 14, click on Insert tab, and find the Line Sparkline button in the Sparklines group In the dialog box that pops up, indicate the Data Range by dragging through the cells that will be represented by the sparkline chart-in this
Adding and Formatting Sparkline Charts 0 Click on 14, click on Insert tab, and find the Line Sparkline button in the Sparklines group Q In the dialog box that pops up, indicate the Data Range by dragging through the cells that will be represented by the sparkline chart-in this case, B4:G4 Click OK o o O • Formatting the Worksheet o In case you forgot to change the Theme to Savon, do it now; otherwise, you may not have the fill colors required for the assignment. • Assign Formats to Nonadjacent Ranges . Use the Format Painter button to format cells O Change the style of the chart Point to the fill handle and drag through cell 116 to copy the Column Sparkline chart SAVE O 0 O O Once a cell is formatted, click on the cell and then double click on the "paintbrush" on the Home tab at the far left Then click on the cell that you want the formatted-the same formatting will be applied to this cell without having to go through all the steps required in previous cell formatting In order to continue formatting more cells, click on the cell, click on the paintbrush, click on the cell to be formatted Another way to format a number of cells is to double-click the O Create a clustered column chart on a separate chart sheet Select the cells that will be illustrated in the chart (A3:G3 and A9:G3) Insert tab, click first clustered column chart in the gallery This is going to be on a separate worksheet so click the Move Chart button The text's instructions enteloor with comard to the paintbrush and then click each cell to be formatted Press Esc to quit format painter Format "What if Assumptions" area SAVE the Workbook
. paintbrush, click on the cell to be formatted o Another way to format a number of cells is to double-click the paintbrush and then click each cell to be formatted Press Esc to quit format painter Format "What if Assumptions" area SAVE the Workbook O O Create a clustered column chart on a separate chart sheet o Select the cells that will be illustrated in the chart (A3:G3 and A9:G3) Insert tab, click first clustered column chart in the gallery o This is going to be on a separate worksheet so click the Move Chart button The text's instructions are very clear with regard to the subsequent editing procedures even though it is rather lengthy To Apply Chart Filters-this is a great feature of Excel 2019 . Color and rearrange worksheet tabs Right clicking the sheet tabs gives you numerous options for making changes to the tab Select both tabs by holding down the CTRL key SAVE workbook • File/Print Change to Landscape Orientation and Scaling to Fit on One Sheet SAVE workbook . Changing the View of the Worksheet This allows you to see part of the worksheet in a different view
o Note that when the windows are split, they have separate scroll bars Freeze and unfreeze titles When making entries in large worksheets with many rows, you must be able to view the column titles and row titles; freezing the titles displays the titles on the screen, no matter how far down or to the right you scroll o o Select B4 Click the View tab and Freeze Panes arrow; choose Freeze Panes Move across the worksheet to see what effect this has on the data being viewed Unfreeze the worksheet columns and rows . Answer what-if questions The power of Excell Management decisions can be made more easily and projections and analysis of data is made simpler o . Goal seek to answer what-if questions o If the Data tab is dimmed and you are unable to Goal Seek, check if both the chart and spreadsheet tabs at the bottom are selected; if so, right-click the Six-Month Financial Projection tab, click Ungroup Sheets on the shortcut menu to deselect the Chart tab-this should allow you to Goal Seek now. . SUBMIT the results of the Goal Seek along with the original workbook