Question

Styles Project Description: You are an accountant for a pharmaceutical sales company. As one of your tasks, you complean annual report that documents regional sales information into one standardized worksheet. As part of this process, you group the worksheets and insert descriptive rows and columns, apply formatting, and insert hundtions. Your last step is to create a sales suminary worksheet and provide basic information for management to evaluate. Steps to Perform: Step Instructions Points Possible 1 0 Open exploring 09 grader_12_Sales xlsx and save it as exploring e09 grader_h2 Sales LastFirst Group the five regional worksheets. Insert a new row above row 1 of the existing data Type Agent in cell A1. Apply the Heading 2 style. 2 5 3 With the five regional sheets grouped, type Qur1 in cell B1. Use the fil handle to add Qu2-Qr4 in cels C1:E1. Apply the Heading 3 style and apply center horizontal alignment to cels B1:51 10 4 With the five regional sheets grouped, type Total in cel F1 Apply the Heading 2 style and horizontal center alignment 10 5 With the five regional sheets grouped, dick cell F2, type - SUM(B2:E2), and press ENTER Use the fill handle to complete the summary information to the range F3.F19. 10 6 With the five regional sheets grouped, type Total in cell A20. In cell B20, insert a function to total Otr1 sales. Copy the function to the range C20-F20. 10 7 With the five regional sheets grouped, format the ranges B2 F2 and B20 F20 with Accounting Number Format and zero decimal places. Format the range B3.F19 with Comma Style and zero decimal places. Add Top and Double Bottom Border to cells B20:F20 10

Answer 1

Answer: Hey dear student find the solution of your query if you have any doubt feel free to ask. Thanks!!

First group your sheets- right click on sheet tab and click select all sheets. They will be grouped. After that start working.

1- Type Agent in cell A1 and apply Heading2 Style

Bookl - Microsoft Excel Insert Home Page Layout Formulas Data Review View Developer Add Ins Cut Calibri 13 Α Α΄ Wrap Text General Paste U- B А M $ - % E Merge & Center Copy Format Painter Clipboard Conditional Format Cell Formatting mas Table Styles Good, Bad and Neutral Number Font Alignment Normal Bad Gd Data and Model A f Agent Check Cell Calculation EX G H A B С D E 1 Agent Quri Qtr3 Total Qt2 778 $ $ 345 2 $ 400 Qur4 567 S 490 1,456 4,598 Output Warning Text Titles and Headings Heading 1 Heading Themed Cell Styles 456 $ 576 890 369 3 He 2,146 1,589 7,690 7,097 123 4,562 1.478 4 782 652

2- type Qtr1 in cell B1 and use Fill handle to add QTR2,Qtr3 in cells C1:E1.Apply heading3 style and center alignment

Booki Microsoft Excel Data View Insert Page Layout Formulas Review Add-Ins Home Developer * Cut Calibri General Α Α΄ 11 Wrap Text La Copy 3Merge & Center $ - % 9 200 .000 I U Format Painter Clipboard Number Font Alignment B1 fr Qtr1 А С D B E G F H Agent Qur1 Qtr2 Qtr3 Qtr4 Total $ 778 Qtr3 2,146 456 $ 576 890 400 345 $ 123 4,562 567 $ 490 1,456 1,589 7,690 782

PICTUUM B С D E G H Calculation 1 Agent 2 Qtr2 Check Cell Qtr3 Explanatory. Input $ 3 4 778 $ 400 Qtri 456 $ 576 890 369 456 325 4,521 345 $ 123 4,562 Qtr4 567 $ 490 1,456 4,598 1,258 782 F Total 2,146 1,589 7,690 7,097 5,725 7,224 8,490 Output Warning Text Titles and Headings Heading 1 Heading 2 Themed Cell Styles 20% - Accenti 20% - Accent2 Heading N Heading 4 652 5 6 7 8 1,478 3,652 4,520 359 1,489 2,365 Heading 3 20% - Accent3 20% - Acce 890 1,235 40% - Accenti 40% - Accent2 369 40% - Accent3 40% - Acce

3- Type Total F1 and apply Heading2 style with centre alignment. click Autosum to calculate sum(B2:E2)

Home Insert Page Layout Formulas Data Review Boold Microsoft Excel Add in View X Cut Developer Calibri 11 Copy Wrap Text Custom Paste Format Painter Clipboard BU AutoSum Merge & Center Alignment %99 Font Conditional Format Cell Formatting Table Styles Styles Sum Insert Delete Format Number Celli Sum) LOOKLP SUM(B2:E2) D B Display the sum of the cells directly after these 3 Qur A 1 Agent 2 3 Qt2 G H 1 Qur3 K M 6 5 $ 456 S 576 890 778 $ 400 782 652 E Qtr4 Total 567=SUM(B2:E2) 490 1,589 1,456 7,690 345 $ 123 4,562 12478 4 5 SUM|5:18 369

Use Fill handle to calculate all totals (B19:E19)

с А F2 for =SUM(B2:E2) A B С D E F ent Qtr1 Qt2 Qtr3 Qtr4 Total $ 456 $ 778 $ 345 $ 576 400 123 890 782 652 369 456 359 4,562 1,478 3,652 4,520 1,235 1,452 325 1,489 2,365 1,452 2,353 1,026 890 456 2,146 1,589 7,690 7,097 5,725 7,224 8,490 6,649 7,977 6,529 4,747 6,755 7,206 576 567 $ 490 $ 1,456 $ 4,5981 $ 1,258 $ 890 $ 369 $ 456 $ 4561 $ 4,562 S 1,478 $ 3,652 $ 4,520 $ 1,235 14,520 4,520 1,235 14,520 60,782 $ 890 4,521 3,289 4,712 365 1,489 2,365 1,452 2,353 1,026 4,520 1,235 14,520 44,919 $ 369 369 456 778 400 782 + 4,562 1,478 3,652 4,520 2,356 652 359 4,520 27,149 $ $ 29,939 $ 79,824

Click Autosum to sum(B2:B19) qtr1 column

19- LOOKUP =SUM HU A B Agent Qtr2 $ Qtri 456 $ 576) 778 400 890 782 369 652 456 359 325 1,489 2,365 1,452 2,353 1,026 890 10 2 3 369 4 4,521 3,289 4,712 365) 1,489 2,365/ 1,452 2,353/ 1,026; 4,520) 1,235) 14,520) =SUM(B2:B19) $ 456 5 16 7 4,562 1,478 3,652 4,520 2,356 29,939 18 9 Lo Total CE

Use Fill handle to calculate total Qtr2,Qtr3,Qtr4

gent Qtr1 Qtr2 Qtr3 $ 456 S 576 345 $ 123 778 $ 400 782 652 890 369 456 359 1,489 2,365 1,452 2,353 1,026 890 4,562 1,478 3,652 4,520 1,235 1,452 456 Qtr4 Total 567 $ 2,146 490 $ 1,589 1,456 $ 7,690 4,598 $ 7,097 1,258 $ 5,725 890 $ 7,224 369 $ 8,490 456 $ 6,649 456 $ 7,977 4,562 $ 6,529 1,478 $ 4,747 3,652 $ 6,755 4,520 $ 7,206 1,235 $ 8,550 14,520 $ 17,806 4,520 $ 13,344 1,235 $ 7,349 14,520 $ 35,916 60,782 Mumm 576 890 369 325 4,521 3,289 4,712 365 1,489 2,365 1,452 2,353 1,026 4,520 1,235 14,520 44,919 369 778 400 456 4,562 1,478 3,652 4,520 2,356 29,939 $ 782 652 359 4,520 27,149 $ Total $ +

Applu Accounting format to B2:F2

Bookl - Microsoft Excel he Insert Page Layout Formulas Data Review View Developer Add-Ins ut Calibri 11 Α Α΄ 2 Wrap Text opy B I U - A ormat Painter ESSE Merge & Center ABC General 123 No specific format a ard Font Alignment St Number 12 456.00 for 456 Currency $456,00 B С D Qtr1 F Total Qt2 Accounting $456. $ 778 $ 400 782 Short Date 31/3/1901 652 359 Long Date Sunday, 31 March, 1901 Time 12:00:00 AM 456 $ 576 890 369 456 325 4,521 3,289 4,712 365 1,489 2,365 1,452 2,353 1,026 4,520 1.225 1,489 2,365 1,452 2,353 1,026 Qtr3 Qtr4 345 $ 567 $ 123 490 $ 4,562 1,456 $ 1,478 4,598 $ 3,652 1,258 $ 4,520 890 $ 1,235 369 $ 1,452 456 S 456 456 $ 576 4,562 $ 890 1,478 $ 369 3,652 $ 778 4,520 $ 400 1,235 $ 782 14,520 $ 652 4,520 $ 2,146 1,589 7,690 7,097 5,725 7,224 8,490 6,649 7,977 6,529 4,747 6,755 7,206 8,550 17,806 13,344 Percentage 45600.00% 890 % 12 10 Fraction 456 369 456 4,562 1,478 3,652 570 Scientific 4.56E+02 More Number Formats...

apply comma style with 0 decimal places B2:B19

Format Cells Number Alignment Font Border Protection 56 $ 576 Sample 44,919 890 Category: General Number Currency Accounting Date Time Percentage Fraction Sdentific Text Special Custom 369 456 Decimal places: 2 Use 1000 Separator) Negative numbers: 1,234 1,234 (1,234) (1,234) 325 521 289 712 365 fx =SUM(B2:319) с D Qu2 Qur3 778 $ 345 $ 400 123 782 4,562 652 1,478 359 3,652 1,489 4,520 2,365 1,235 1,452 1,452 2,353 456 1,026 576 890 890 369 369 456 778 4,562 400 1,478 782 3,652 652 4,520 359 2,356 4,520 29939 27149 E Qtr4 567 $ 490 $ 1,456 $ 4,598 $ 1,258 $ 890 $ 369 $ 456 $ 456 $ 4,562 $ 1,478 $ 3,652 $ 4,520 $ 1,235 $ 14,520 $ 4,520 $ 1,235 $ 14,520 $ 60782 Total 2,146 1,589 7,690 7,097 5,725 7,224 8,490 6,649 7,977 6,529 4,747 6,755 7,206 8,550 17,806 13,344 7,349 35,916 162789 489 365 452 .353 ,026 ,520 235 Number is used for general display of numbers. Currency and Accounting offer specialized formatting for monetary value. ,520 OK Cancel 1919

Apply border

Bookl - Microsoft Excel Insert Page Layout Formulas Data Review View Developer Add-Ins Σ AutoSuι Number Fill - ру Calibri - 11 -A A B I UDB--A Wrap Text *Merge & Center .09 $ % Insert Delete Format mat Painter 2 Clear Conditional Format Cell Formatting - as Table - Styles Styles rd Font Alignment Number Cells ? х A Format Cells fx =SUM(B2:319) С D Number Alignment Font Border Fill Protection E F Line Presets Qtr2 Total $ 778 $ 400 Style: None None Qutine Inside 782 Border 652 Qtr3 345 $ 123 4,562 1,478 3,652 4,520 1,235 1,452 456 576 Text Text B Quri 456 $ 576 890 369 456 325 4,521 3,289 4,712 365 1,489 2,365 1,452 2,353 1,026 4,520 1,235 14,520 44,919 Color: Automatic Qtr4 567 $ 490 $ 1,456 $ 4,598 $ 1,258 $ 890 $ 369 $ 456 $ 456 $ 4,562 $ 1,478 $ 3,652 $ 4,520 $ 1,235 $ 14,520 S 4,520 $ 1,235 $ 14,520 $ 60,782 359 1,489 2,365 1,452 2,353 1,026 890 369 456 4,562 1,478 3,652 4,520 2,356 29,939 2,146 1,589 7,690 7,097 5,725 7,224 8,490 6,649 7,977 6,529 4,747 6,755 7,206 8,550 17,806 13,344 7,349 35,916 162,789 890 369 The selected border style can be applied by diding the presets, preview diagram or the buttons above. 778 400 782 652 359 OK Cancel 4,520 27,149

changes in all sheets because of grouping

Region1

F19 fx =SUM(B19:119) B с E G А Agent Qtr2 $ Qtr1 456 $ 576 778 $ 400 890 782 369 652 456 359 Qtr3 345 $ 123 4,562 1,478 3,652 4,520 1,235 1,452 456 576 325 1,489 2,365 1,452 2,353 1,026 890 Qtr4 567 $ 490 $ 1,456 $ 4,598 $ 1,258 $ 890 $ 369 $ 456 $ 456 $ 4,562 $ 1,478 $ 3,652 $ 4,520 $ 1,235 $ 14,520 $ 4,520 $ 1,235 $ 14,520 $ 60,782 F Total 2,146 1,589 7,690 7,097 5,725 7,224 8,490 6,649 7,977 6,529 4,747 6,755 7,206 8,550 17,806 13,344 7,349 35,916 162,789 1 890 N 3 369 4,521 3,289 4,712 365 1,489 2,365 1,452 2,353 1,026 4,520 1,235 14,520 44,919 369 1 456 778 400 UI 5 782 652 4,562 1,478 3,652 4,520 2,356 29,939 359 3 3 4,520 27,149 Total 1 2 m 11 Regioni Region2 Region3 Region4 Region5 eady

Region2

A20 fx Total A B с D F 1 Agent Qtr2 E Qtr4 567 $ Total 2. $ $ Qtr1 456 $ 576 3 778 $ 400 782 490 4 890 5 369 652 1,456 4,598 1,258 890 6 456 Qtr3 345 $ 123 4,562 1,478 3,652 4,520 1,235 1,452 456 576 890 7 325 8 369 359 1,489 2,365 1,452 2,353 1,026 890 9 456 10 456 11 12 2,146 1,589 7,690 7,097 5,725 7,224 8,490 6,649 7,977 6,529 4,747 6,755 7,206 8,550 17,806 13,344 7,349 35,916 162,789 13 369 369 4,521 3,289 4,712 365 1,489 2,365 1,452 2,353 1,026 4,520 1,235 14,520 44,919 $ 14 456 778 15 400 4,562 1,478 3,652 4,520 1,235 14,520 4,520 1,235 14,520 60,782 $ 16 782 4,562 1,478 3,652 4,520 2,356 29,939 $ 17 18 652 359 4,520 27,149 $ 19 20 Total $ 21 22 23 24 Regioni Region2 Region3 Region4 Region5 nanchi

Region5

A20 fic Total B с D E G A 1 Agent 2 F Total Qtr2 Qtr3 Qtr4 $ Qtr1 456 $ 576 345 $ 567 $ 778 $ 400 3 123 490 4 890 782 5 369 652 1,456 4,598 1,258 890 6 456 7 8 4,562 1,478 3,652 4,520 1,235 1,452 456 576 890 369 9 10 11 12 325 4,521 3,289 4,712 365 1,489 2,365 1,452 2,353 1,026 4,520 1,235 14,520 44,919 $ 13 14 15 16 17 18 19 20 Total 359 1,489 2,365 1,452 2,353 1,026 890 369 456 4,562 1,478 3,652 4,520 2,356 29,939 $ 2,146 1,589 7,690 7,097 5,725 7,224 8,490 6,649 7,977 6,529 4,747 6,755 7,206 8,550 17,806 13,344 7,349 35,916 162,789 456 456 4,562 1,478 3,652 4,520 1,235 14,520 4,520 1,235 14,520 60,782 $ 369 778 400 782 652 359 4,520 27,149 $ $ 21 22 23 Region1 Region2 Region3 Region4 Region5 Ready