### Data And Excel Format – Computer Skills

This chapter covers Data And Excel Format-Computer.

Center the contents of a cell horizontally

1. click on cell

2. click on center horizontal button on home screen in second row

Apply the bad style to a cell

1. click on the cell

2. click on cell styles

3. chose bad style

Apply the Accounting number format, using the dollar sign ($) and two decimal places, to a cell range (ex: C4:C15)

1. highlight cells

2. right click on cells

3. click on format cells

4. click on number, then choose accounting.

5. make sure to choose 2 decimal places and dollar sign

Apply the comma style number format to a cell range (ex: C4:C15)

1. highlight cell range

2. click on number (in home page)

3. choose the comma button

Bold a cell

1. click on cell

2. click on the dark B (bold button) under the font

Format column (ex: d) using auto fit so that all cell content is visible

1. click on column

2. double click on column line

Change the color of the font in a cell (ex: D9) to blue (standard colors section, column 8)

1. click on cell

2. go into home and click the letter with the underlined color

3. choose the desired color

Change the font of a cell to cambria

1. click on the cell

2. click on the font style button arrow and change to desired font

Change the size of the font of a cell

1. click on cell

2. click on font size number and change to desired font size

Without using parentheses, enter a formula in cell G4 that determines the Projected 2021 Salary, less Healthcare, for the employee by taking the value in cell C4, adding the product of cell C4 multiplied by cell E4, and then subtracting the value in cell F4.

1. click on cell G4

2. type in the equal sign (=)

3. click on cell C4

4. type in the plus sign (+)

5. click on cell C4 (again)

6. type in the multiplication sign (*)

7. click on cell E4

8. type in subtraction sign (-)

9. click on cell F4

10. hit enter button

In a cell, enter a formula using the SUM function to total the values in a cell range (B4:B15)

1. click on a cell

2. type in the equal sign (=)

3. type in SUM(

4. highlight cell range

5. hit enter

Merge and center the contents of cell range (ex: A2:D2)

1. highlight cell range

2. click on merge and center in alignment tab

Rename the sheet 1 worksheet with the new name “Sales”

1. right click on the worksheet

2. click on rename

3. type in new name “Sales”

4. hit enter button

Enter the text “2020 Sales” in the bar chart title placeholder

1. click on chart title on bar chart

2. delete current title

3. enter the text “2020” sales

4. click any cell

Change the color of the income statement sheet tab to green (standard colors section, column 6)

1. right click on the income statement sheet tab

2. click on tab color

3. choose desired color

Apply chart style 5 to the chart

1. click on chart

2. click on design tab

3. choose desired chart style

Clear the contents of cell b7

1. click on cell

2. right click on cell

3. click clear contents

Move the Stacked Column chart in the Sales Forecast worksheet to cell F1 on the Category Sales worksheet.

1. click on chart

2. right click on chart

3. click move chart

4. change new worksheet to desired worksheet (category sales)

In cell b7, enter a formula using relative cell references that subtracts cell b6 from cell b5

1. click on cell

2. type in equal sign

3. click cell b5

4. type in subtraction sign

5. click on cell b6

6. hit enter

Use the fill handle to copy the formula in cell B7 to cells C7 and D7.

1. click on cell b7

2. click and drag right hand corner square (fill handle) to c7 and d7

Insert a 3-D Pie chart into the worksheet, based on range A4:B7.

1. highlight cell range

2. click on insert tab

3. choose 3-d pie chart

Select non-adjacent cells C4, C6, and C11.

1. click on cell c4

2. hold control button while clicking c6 and c11

Move the Stacked Column chart to a chart sheet; accept the default chart sheet name.

1. click on chart

2. right click on chart

3. choose move chart

4. click on new sheet

5. click ok

Enter the number 2100 in cell B12

1. click on cell b12

2. type in number 2100

3. click on a different cell or hit enter

Apply the Berlin theme to the current workbook.

1. click on page layout tab

2. click on themes

3. choose desired theme (berlin theme)

Apply the Percentage number format, with no decimal places, to range B7:D7.

1. highlight cell range

2. choose number

3. click on percent sign

Change the width of column C to exactly 15 characters.

1. right click on desired column

2. choose column width

3. type in desired column width size and hit ok

Format column d using auto fit so that all cell content is visible

1. double click on column line

Change the fill color of cell d9 to blue, accent 1, lighter 60% (theme colors section, row 3, column 5)

1. click on cell

2. choose fill tab arrow

3. choose desired color

Change the size of the font in cell a2 to 11 points

1. click on cell a2

2. change font size to desired size

Change the page orientation of the current worksheet to Landscape.

1. click on page layout tab

2. choose orientation

3. click landscape

Enter a formula using arithmetic operators and parentheses in cell B14 that adds the monthly expenses in cells B9, B10, and B11, and then multiplies that result by 12.

1. click on cell

2. click on editing and autosum

3. highlight cell range

4. close parenthesis and multiply by 12

5. hit enter

In cell B16, create a formula using the AVERAGE function to calculate the average of the values in the range B4:B15.

1. click on cell

2. click on editing and choose average

3. highlight cell range

4. hit enter

In cell E16, create a formula using the MAX function to calculate the maximum value in the range E4:E14.

1. click on cell

2. type in equal sign and MAX with open parenthesis

3. highlight cell range

4. close parenthesis and hit enter

In cell E17, create a formula using the MIN function to calculate the minimum value in the range E4:E14.

1. click on cell

2. type in equal sign and MIN with open parenthesis

3. highlight range

4. close parenthesis and hit enter

In cell B16, create a formula using the SUM function to total the values in the range B4:B15.

1. click on cell

2. click on editing and choose autosum

3. highlight range

4. hit enter

For range B4:B15, create a New conditional formatting rule that formats the top five values in the range with a Green, Accent 6 cell background color (last color option in the top row of colors).

1. highlight cells

2. click on conditional formatting

3. choose new rule

4. choose format only top or bottom ranked values

5. choose top and type in amount of values

6. choose format and choose the correct color

7. hit okay

Apply thick outside borders to the range A8:D8

1. highlight cells

2. choose border option button

3. click on thick outside borders

Apply the Currency number format, using the $ symbol and showing two decimal places, to range C4:C15.

1. highlight cells

2. right click and choose format cells

3. choose currency format

4. make sure it is two decimals and dollar sign

5. hit okay

Apply the 03/14/12 Date number format to cell D4.

1. click on cell

2. right click and choose format cells

3. choose correct format under date and hit ok

Change the height of row 2 to approximately 27 points (36 pixels)

1. right click on row

2. click on change row width

3. type in desired width

4. hit okay

For values in the range B3:B12, adjust the number of decimal places so that two decimal places are displayed

1. highlight cell range

2. right click and choose format cells

3. move 2 decimal points

Change spelling in the entire worksheet, accepting the suggested correction

1. click review sheet

2. click spelling

3. accept change

Change the worksheet margins to the Narrow margin style.

1. click on page layout

2. click on margins

3. click on desired margin

In cell b16, use the keyboard to enter a formula that uses the SUM function to calculate the total of the values in range B4:B15

1. click on cell

2. type in =SUM(

3. highlight cell range

Use Flash Fill to fill range E4:E15 after typing DeanLu@GlacierIce.com in cell E4 and Lau in cell E5

1. type in required information

2. type in three letters and hit enter for flash fill

In cell B7, enter a formula using relative cell references that subtracts cell B6 from cell B5.

1. click on cell

2. enter formula for subtracting

Print range A3:F8

1. highlight range

2. click print

3. change to selection

Add the text Confidential to the center header section, and then click any cell to deselect the header.

1. click on view

2. click on page layout

3. click on the center header

4. type desired text

Change the scale to 90%

1. click on page layout

2. change scale to 90%

Enter a formula in cell d5 that divides the value in cell c5 by the value in cell c17, using an absolute cell reference to cell c17

1. click cell

2. type in cell divided by next cell

3. hold fn button and do f4 button

4. hit enter

In cell F4, enter a formula using the IF function that returns a value of YES if cell E4 is greater than 0 (zero), and a value of NO if not.

1. click on cell

2. click on small fx box

3. choose select category

4. choose logical and hit okay

5. type E4>0 for logical test box

6. type in yes for true, no for false, and hit ok!

Rotate the contents of the cells in range B3:M3 to exactly 45 degrees.

1. highlight cells

2. click on alignment and choose the small arrow in the corner

3. type in 45 for degrees

Add chart title chart element to pie chart

1. click on chart

2. hit green plus sign

3. click chart title

Add the data labels chart element to the Bar chart at the Outside End position.

1. click on chart

2. click on green plus size

3. click data labels and choose outside end

Change text direction of vertical axis to horizontal

1. right click on vertical title and choose format

2. choose alignment

3. change text direction to horizontal

Split worksheet

1. click on cell

2. click on view tab

3. choose window and click split

Freeze column

click on cell to the right of/bottom of column/row

Insert a Line type Sparkline in cell E5 using the Data Range B5:D5.

1. click on cell

2. click insert sparkline

3. highlight cells

Use goal seek to calculate the changing value in cell b5 that will result in a set value in cell b7 of 0.3, or 30%

1. click on data

2. choose forecast, what if analysis, goal seek

3. type in information