Excel mindtap (SBU computer & info)
In page layout view , set custom margins by changing the left and right margins to 0.5 inches. Do not change the top and bottom margins.
page layout (in same row as file and home) margins (top left) custom margins in the farthest left blank edit to 0.5 and the one across form it OK
On the vertical axis of the line chart, define 20000 as the major units and 5000 as the minor units
same as above except in major blank type "20000" hit enter in minor blank type "5000" hit enter
apply bad style to cell to B8
select cell go to cell styles in style in top middle/right
apply 03/13/12 date number format to cell d5
select cell go to small arrow in the bottom right corner of number select the correct date format and click ok
To move the contents of cell B4 to F10 cut and paste
select cell B4 click scissors in top left (under file) click F10 click clipboard in top left (under file)
insert a page break between row 18 and 19 of the worksheet
select cell in row 19 (last) go to page layout click "breaks" in page setup drop down to "insert page break"
rotate the contents of the cells in the range A3:D3 to exactly 45 degrees, which is three points up and to the left of zero degree marker on the orientation arc
select cells go to small arrow in the bottom right corner of alignment move the orientation marker up to 45 degrees click ok
To create a new Conditional Formatting Rule that formats the top 5 values with a blue accents 5 background color
select cells go to conditional formatting in styles in middle drop down to New Rule drop down to "format only top or bottom ranked values" change number 10 to 5 then click format button go to "fill" tab, select color, and hit ok click ok again
use keyboard to enter SUM function to calculate total values
TYPE =SUM(B5:B16)
apply accounting number format using the $ sign and two decimal places
select cells and click $ in middle in number
apply percentage number format with no decimal places
select cells and click % in middle in number
Apply the Comma Style number format to the selected cells.
select cells and click , icon in middle in number
column width
select entire C column (click the c) click format in cells in top right of page select "column width" in dropdown type 15 and press ok
hide column f
select entire D column (click the D) click "format" in cells in top right of page click "hide & unhide" (under visibility) scroll over to click hide columns
select non-adjecent cells
select first cell hold control select second cell let go hold control select third cell let go
Link the data in range A2:C6 to the Rockland Services.docx document. Insert the Excel data in the word document directly under the last line of text and use the Link & Keep Source formatting paste option to create the link
select range copy word document icon (bottom) paste option with the paintbrush
In cell A4 type a formula using TRANSPOSE function to transpose the data in range A17:J21 into range A4:E13
select range type =transpose(A17:J21) press control + shift + enter
Create a forecast sheet based on range A1: B6
select range data forecast sheet (far right) create
Create a defined name for range A4:E18 using the create from selection command. Use the titles in the top row of the selection as the range names. Select the years range using the name box.
select range formulas create from selection (middle) ok A4 dropdown (far left, middle of screen) type Years enter
Assign the defined name Sales2020 to range C5:C13
select range formulas define name Name: Sales2020 ok
Assign the defined name Sales2020 to range C5:C13
select range formulas (in same row as file) define name (middle) Name: type Sales2020 ok
Create defined names for range A4:E18 using the create from selection command. Use the titles in the top row of the selection as the range names. Select the "years" name using the name box.
select range formulas (same row as file) create from selection (middle) ok left side in A4 dropdown type "Years" enter
Insert a map chart in the worksheet based on range A5:B11
select range insert recommended charts (middle) map option ok
Insert a scatter with straight lines into the worksheet based on range A4:C9. Use the second option on the second row of the scatter section in the enter scatter (X,Y) bubble chart gallery
select range insert scatter plot graph button (in the middle) last option in 2nd row
change color of the Sales by Branch sheet tab to green
select sheet title at bottom of page right click drop down to "tab color" select color
without using parentheses enter a formula create a formula: type = then equation
type = click cell + * - click check mark at left/middle next to the x =b1+b1*b2-b3
On the vertical axis of the line chart, define 20000 as the minimum bounds and 140000 as the maximum bounds. Do not include a currency symbol when entering values for bounds.
click vertical axis (left axis) on graph right click it drop down to "format axis" in minimum blank type "20000" hit enter in maximum blank type "140000" hit enter
change the zoom level of the worksheet
click view (top middle of page) click zoom (middle to the left of page) select the % from drop down click ok
Transpose the data in range G4:K7 and paste it into cell K3
copy and past use the clip board with the small arrow to the right (top section, bottom row)
Create a scenario summary report showing result values for cells D5, D9, and D13 in the report.
data What-If analysis Scenario manager Summary... type D5, D9, D13 ok
Create a scenario with the changing cells in J4 and J6 that increase price by .03 (3%) in the cell J4 and the units sold by .04 (4%) in cell J6. Use Moderate as the scenario name and show the scenario results in the worksheet.
data What-If analysis Scenario manager add... Scenario name: Moderate Tab changing cells: j4,j6 ok $j$4: .03 tab 4j$6: .04 ok select Moderate show
Edit the Bonus Only scenario by using J4:J5 as the changing cells with new values of 0 (zero) for cell J4 and .15 (15%) for cell J5. Show the scenario results in the worksheet.
data What-If analysis Scenario manager select Bonus Only Edit... ok $j$4: 0 tab 4j$5: .15 ok show
Create a scenario PivotTable report showing results cells J4:J6
data What-If analysis Scenario manager Summary... check the scenario PivotTable report bubble type j4, j5, j6
Add a constraint to the current solver model specifying that 900 is the maximum value for cell D16
data solver (far right) Add Call reference: D16 Constraint: 900 ok close
Create a solver answer report for the current solver model, then view the report results
data solver (far right) solve answer (right side of box) ok click answer report 1 at the very bottom of the screen
Create a solver model with a target value of 200000 for cell E28 and cell D16 as the variable cell. Run the solver model and accept the default to keep the results, then close the solver results dialog box.
data solver (far right) top textbox E28 select Value of bubble in the blank beside it type 200000 next blank type D16 solve ok
Open the Rockland Financials.xlsx file and update the external data links
double click update
In the histogram chart, edit the bin size to be 15.0 data point and then close the format axis pane.
double click the bottom axis of graph click the three bars option click "axis options" fill in the "bin width" bubble fill in the blank directly across with "15.0" click tab exit the box
Save the current workbook as an excel workbook with Project sales, as the new file name, and store the new file in the documents folder
file save as change first blank to say "Project Sales"
insert new row above row 7 delete row
find numbers on far left of page select number with black arrow showing up click "insert" in cells top middle/right of page same for delete just click delete by insert
change the height of row 2 to approximately 27 points
find the numbers at the far left side of page look for black plus shaped thing at the bottom of a number and drag down
Delete the defined name locations from the worksheet
formulas (same as file row) Name manager (middle) Delete (top) ok close
change the page orientation of the current worksheet to landscape
go up to page layout in same row as file and home at top of page clock orientation in page setup on top left of page click landscape
change the scale of the worksheet to 70%
go up to page layout in same row as file and home at top of page find "scale" in fit to scale in middle/left of page type 70 enter
change the worksheet margins to...
go up to page layout in same row as file and home at top of page go to "margins" in top left in page setup click narrow
Group the October, November, and December worksheets. In cell B9 of the October worksheet use the AutoSum button to total the values in the range B6:B8. Ungroup the worksheets and switch to the November worksheet.
in bottom left click October press & hold SHIFT click December click B9 AutoSum (top right) click checkmark button (top left between x and fx) right click october (at bottom) "ungroup sheets" (drop down to bottom) click November
use Flash Fill to fill
in first cell type email in next cell type "Sal" enter
use autofill to fill range D12:D19 with only the formatting from cell D11
D11 black + drag down upside down L button in bottom right corner of the selected range "fill formatting only"
Insert a comment with the text Please update in cell D12
D12 review new comment (middle left) type Please update send button
create a two varety data table in range D3:H11 in cell D3 type =B7 as the formula reference cell In the data table dialog box use cell B4 as the column input cell.
D3 type =b7 enter select range data What-If analysis Data table... Row input cell: B6 column input cell: B4 ok
In cell D9 edit the define name TotalSales_2021 by typing "Total_2021" as the new name.
D9 formulas (same as file row) Name manager (middle) TotalSales2021 (bottom one) Edit type "Total_2021" ok close
Add the solver add-in to the workbook
Data File home options (bottom of green strip) Add ins (left side) Go... (bottom) select Solver add in ok
Show the results in the following scenarios in the worksheet: the best case scenario, and the worst case scenario, the moderate case scenario. Close the scenario manager dialog box when you are finished.
Data What-If analysis (far right) scenario manager Best case show worst case show moderate case show close
Insert the check box (form control) into cell E6 (insert menu, form controls section, top row, option 3)
Developer (in same row as file and home) Insert (with toolbox icon, under data) select check box icon select cell E6(lower right corner drag to upper left) if that doesn't work just try and click top left corner
In cell E5 create a formula using structured references that multiplies the bonus % in cell C5 by the 2020 sales value in cell D5
E5 type =[Bonus %]*[2020 Sales] enter
Save the current workbook as a template with the ".xltx" file extension and using "Client Contacts" as the file name. Accept the default file save location
File save as Docyments bar by the blue arrow (top) for File Name: type "Client Contacts" in the next dropdown select Excel template (*.xltx) save
Trace errors in the formula in cell G10 and G13
G10 formulas (same as file row) Error checking dropdown arrow (right) Trace errors do the same for G13
delete worksheet
In top right part of page in cells click delete dropdown to delete sheet (bottom of dropdown) click delete
Create a conditional formatting rule to highlight duplicate values in range A5:J18 with yellow background fill color (background color section, bottom row, 4th option)
Select range conditional formatting (middle) Highlight cell rule duplicate values dropdown arrow to custom format fill tap (top right) bottom row yellow ok ok
insert a line sparkline in cell E5 using the data range B5:D5
click cell E5 click the insert tab in the same row as file and home click the sparklines button in the middle dropdown to line select the range of cells click ok
Use the tell me box to change the FILL COLOR of cells B4:D4 to BLACK, text 1 (theme colors, top row, 2nd)
click center of cell pull to end of desired location while it is selected go to tell me what to do bar type "Fill Color" and select black
Link the excel bar chart to the powerpoint slide with the use destination theme & link data past option
click chart copy powerpoint icon (bottom) past dropdown middle option w colored bars
Insert a linear forecast trendline in the column chart that forecasts forward by 2.0 future periods
click chart design Add chart element (far left) trendline more trendline options scroll down to bottom change the forward text box to 2.0 exit box
Add a chart title element to the chart in the default location above the chart
click chart design (same row as file) Add chart element (far left) Chart Title Above Chart
On the Bar Chart, add the title "Dollars" to the horizontal axis and the title "Client Name" to the vertical axis, then click on the chart
click chart click green + to the right drop down to check Axis Title type "Dollars" in the bottom Axis Title type "Client Name" in the side Axis Title click cell C3
Apply a solid line boarder using a default line color to the pie chart legend. (close the pane and select outside of chart to complete
click graph blue + beside it black arrow across from legend more options paint bucket button Boarder solid line exit box click out
Change teh layout of the column chart to layout 2 (top row, 2nd option in the quick layout gallery)
click graph click design tab click "quick layout" on the far left side click 2nd option
Add primary major horizontal gridlines to the clustered column chart
click graph click green + at the top right corner of chart drop down to click gridlines
delete the legend from the pie chart
click graph click green + at the top right corner of chart drop down to click legend
in the line chart, move the chart legend to the right position
click graph click green + at the top right corner of chart drop down to legend (don't click) find black arrow at the right end drop down/over to "Right"
use the mouse to move the chart legend to the upper right corner of the column chart
click graph drag the key of chart to the top right corner
In the bar chart switch the data shown in the rows and columns
click graph go to design click "switch row/ column" in middle right of top
Add a linear trendline to the bar chart
click graph go to design tab in same row as file and home click "add chart element" -first option on left drop down to trendline drop down to linear
In the clustered column chart, define the range A5:A8 as the horizontal axis label values. Enter the range in the axis labels text box.
click graph go to design in the same row as file and home click "select data" on the right side in data click the "edit" button on the left side in the middle select range click ok click ok
Group the 1dt quarter, 2nd quarter, 3rd quarter, and 4th quarter worksheets, then for range A4:E4 in all the grouped worksheets, apply the italic formatting to the text and change the fill color of the cells to black, text 1 (theme colors section, row 1, option 2)
1st quarter (bottom left) hold SHIFT 4th quarter select range A4:E4 italicize (top left) change color click 2nd quarter (bottom left)
Fill the range A10:A13 with a custom list of branch names, typing "Deerfield" in cell A10 as the first entry in the list
A10 Type Deerfield in bottom right corner black + pops up drag down to A13
In cell A20, insert a hyperlink that displays the test "Click here for more details" and links to monthly sales.xlsx workbook
A20 Insert (same row as file) link Existing File or Web Page type in text to display"Click here for more details" Monthly sales.xlsx ok
In the pivottable create a relationship between the consultants table and the related bill table using the last field as a foreign column
A3 CREATE button table: consultants related table: billed column foreign: last ok
Remove the Region field from the filters area of the PivotTable fields pane, removing the fields from the PivotTable entirely .
A4 Region (left side of box) drag Region onto the worksheet empty area
In the PicotTable change the number format of the sum of the January field to the accounting number format with zero decimal places.
A5 click Sum of January (right side of box) Field settings (bottom of dropdown) Number format Accounting Decimal Places: 0 ok
in cell D19 create a formula using the MAX function to calculate the maximum value in range D4:D17
click in cell D19 click the "fx" button under the fonts category on the left in the dropdown for "or select a category" dropdown to "statistical" in the select a function dropdown dropdown to MAX type D4:D17 in Number1 click ok
In the formula in cell B20, replace the existing cell address with the defined names "Revenues_2020", "Direct_Costs_2020", and "Operating_Expenses_2020"
B20 formulas (same as file row) Define name dropdown arrow (middle) Apply names select all 3 names ok
In cell B6 enter the formula using defined names to subtract direct cost (cell B5) from revenues (cell B4)
B6 type =Revenues-DirectCosts enter
In cell B6 of the Q4 worksheet, enter the formula using a 3D reference and the SUM function to total the values of B6 of the October, November, and December worksheets
B6 type =SUM( october (bottom left) hold SHIFT December B6 click checkmark button (top left between x and fx)
Break the link, found in cell B8, between the current workbook and the Rockland Expenses.xlsx workbook
B8 data (same row as file) edit links (right below) break line button break link close
In cell B8 of the Branch sales workbook, enter the formula, using external references, that adds together the values from cells B8 in all sheets of the monthly workbook sales
B8 (left screen) type = click right screen B8 type + February (bottom) B8 (on right screen) + March B8 click checkmark button (top left between x and fx)
In cell C6, enter the formula that multiplies the values in C5 for the month of January by the value in cell B6. Used a mixed reference for the cell B6 so that only column B does not change when copied. Use relative reference for all other cell references.
C6 type =C5*$B6 enter
Apply Berlin theme to current
Click page layout in same row as file and home at top of page click themes far left
Apply the blue accent 5 outline color (theme colors selection first row, 2nd option from right) to the red call out shape. Then apply a line weight of 3pt.
click shape go to format in same row as file and home click "shape outline" in shape styles click the color then click "shape outline" again and drop down to weight at the bottom drop down to 3 pt
insert a new worksheet to the right of the CURRENT CLIENTS worksheet
click small + at bottom of page after titles
change the text direction of the vertical axis title Client Name to horizontal
RIGHT click the side axis title "client name" go down to "Format Axis Title..." three options should pop up chose the 3rd grey box option click alignment in the "Text direction" dropdown click horizontal exit out of box
Edit the hyperlink in cell A24 by adding "Go to BLS website" as the ScreenTip
Right click A24 drop down to "edit hyperlink" (middle) screentip button (right) type "Go to BLS website" ok ok
In cell A24, insert a hyperlink to the www.bls.gov website and enter "Click here for BLS wage data" as the hyperlink text. (DO not create a screen tip)
Right click A24 drop down to "link" (bottom) In Address: type www.bls.gov in Text to display: type "Click here for BLS wage data" ok
Apply chart style 5 to chart
click the right chart go to the design tab in the same row as rile and home go to the bottom right corner of the chart styles and click the down arrow select the 5th option
Filter the Pivotchart on the branch field to display only the records with a value New York
click Branch dropdown (bottom of chart) click Select All box to unselect select New York box ok
Create a one variety data table in range D5:G13.In cell D5 type =B4 as the input cell. In cell G5 type =B9 as the formula results value. In the data table dialog box use cell B4 as the column input cell.
click D5 type =B4 enter click G5 type =B9 enter select range data What-If Analysis (far right) Data table... in column input cell type B4 okay
In the chart on the sales worksheet change the series name of the Deerfield series field to "Chicago" by modifying the SERIES formula for the data series (include the "quotation marks"
click bottom blue bar of chart in the formula bar at the top type "Chicago" where the first Sales ---- is enter
In cell B8 create a formula using external and internal worksheet references, that subtract cell B14 on the operating expenses worksheet of the Rockland expenses.xlsx file from cell B6 on the income statement worksheet of the Rockland Financials.xlsx file
click cell B8 type = click B6 type - view (in same row as file and home) switch windows (on right) click 2 Rockland... click B14 enter
Use the AutoFill feature to fill the range A4:A15 with the names of the months in chronological order with January in cell A4
click cell A4 type January from the bottom right corner of cell A4 drag down
apply thick outside boarders to range A8:D8
click cell A8 hold and drag across to D8 go to four square looking thing in top left in font dropdown to thick outside boarders
In cell B2 create a formula using the TODAY function to display the current date
click cell B2 go to formulas tab at the top middle click "date & time" right underneath dropdown to TODAY click ok
use the Goal Seek to calculate the changing value in cell B5 that will result in the set value in cell B20 of $1,000
click cell B20 go to "data" tab at the top middle go to the "what-if analysis" button on the far right drop down to Goal Seek in "to value" blank type "1000" tab to next blank click cell B5 click ok
use the fill handle to copy the formula in B7 to cells C7 and D7
click cell B7 select B7:D7 from the bottom right corner of B7
use AutoFill to fill range D12:D19 with only the formatting from cell D11
click cell D11 from the bottom right corner of cell D11 drag down a button should pop up in the bottom right corner of selected cells with a small blue box in it drop down to "fill formatting only"
in cell D21 enter a formula using the MEDIAN function to calculate the median value in range D4:D18
click cell D21 click the "fx" button under the fonts category on the left type "median" in the first textbox click go and click ok again type D4:D18 in the Number1 blank click ok
in cell E5 create a formula using the VLOOKUP function to determine the bonus percentage for employee Wanda Allen based on the performance rating in cell C5. Use range G4:H10 as the lookup table and the bonus percentage listed in column 2 of the lookup table. Do not enter a value for the optional range_lookup argument.
click cell E5 type =VLOOKUP(C5,G4:H10,2) click enter
To the sparklines in range E5:E8 add a green marker to the high point (standard colors, 6th option) and add red marker to the low point (standard colors, 2nd option).
select range of blue lines beside table go to design is same row as file and home click "marker color" in far right of style drop down to "high point" then the color click "marker color" again drop down to "low point" then color
Rename the sheet 1 worksheet using SALES as the new name
select format in top right part of of cells at top of page drop down to rename sheet In bottom left corner type "Sales" press enter
add gradient fill green data bars to range B4:B15
select range click conditional formatting drop down to "Data Bars" top middle green option
Insert a clustered column - line combo chart in the worksheet based on range B3:D17. Plot the years as client data series on a secondary axis.
select range go to insert in same row as home and file click "recommended charts" switch to "all charts" tab at the top of the box go down to "combo" at very bottom check the "years as clients" box click ok
Insert a histogram chart in the worksheet based on range D5:D13
select range go to insert in same row as home and file go to the all blue bars/ skyscraper things in charts click the first option
For range D5:D8, define 75000 as the maximum data bars value. Do not include a currency symbol when entering values.
select range go to conditional formatting drop down to "data bars" drop down to "more rules" at very bottom in the Type: click the "maximum" dropdown arrow drop down to "number" in the blank below type "75000" click ok
Insert the stacked column chart in the worksheet based on range A4:D8 (2nd option in the 2d column section of the gallery)
select range go to insert click first option in charts (looks like skyscraper buildings) select 2nd option in 2D
Insert a scatter chart (the first thumbnail in the scatter section in the insert scatter (X Y) or bubble chart option) in the worksheet based on range D4:E10.
select range go to insert click the scatter plot chart button in the middle row at the bottom (in charts) select the first option
Insert a 3D clustered column chart (the first 3D column chart option) in the worksheet based on range A4:D8
select range go to insert in same row as home and file click first option in charts (looks like skyscraper buildings) 1st column, 2nd row
Choose the Excel's Recommended Charts option to insert a stacked column chart in the worksheet based on range A5:D8
select range go to insert in same row as home and file click recommended charts choose 2nd option click ok
Insert a 3D pie chart into the worksheet, based on the range A5:B16
select range go to insert in same row as home and file click the pie chart option in charts select the option in the 2nd row
change the style of the sparkline in the cell E5 to orange, sparkline style accent 2, (no dark or light)(the 2nd option in the 3rd row of the sparkline style gallery)
beside the chart select the top row of square shapes go to design in the same row as file and home in style scroll down select the middle orange option
Enter the text "Sales to Date" in the Bar chart title placeholder
click "Chart Title" blank on right type "Sales to Date" click cell A18
print selection
click and drag to select section of cells click file click print click print active sheets dropdown to print selection click print
clear contents of cell A7
click cell click "clear" (with purple eraser) in editing in top right of page in dropdown click "clear contents"
wrap text
click cell click wrap text (middle of page in alignment)
create a formula using SUM function to total values
click cell click "autosum" in editing on the top right of page enter
Insert a clustered column pivotchart in the current worksheet using the recommended charts button
click cell A2 click insert click "recommended charts" click ok
Enter the formula in C6 that divides the value in B6 by the value in B18, using an absolute cell reference to cell B18
click C6 type = select B6 type / select B18 press F4 press enter
In cell B6 create a formula using the ROUND function that rounds the value in cell J6 to an integer, with zero decimal places
click Cell B6 type =ROUND( click cell J6 type ,0) click the check mark in between the x and fx at the top
Align contents of cell vertically using the BOTTOM ALIGN option
above to align text to center or to the right select the last box
reorder the worksheets so that 3 year sales forecast worksheet is the last worksheet in the workbook
at the bottom of the page next to the arrows hold down with left mouse on the title that needs to move move with other hand look for the small black triangle place before the "..." (if last in workbook)
In cell F4 enter a formula using the IF function that returns a value of YES if cell E4 is greater than 100,000 and a value of NO if not
click cell F4 go to formulas tab at the top middle click "insert function" on the far left in the dropdown for "or select a category" dropdown to "logical" in the select a function dropdown dropdown to "IF" click ok in "logical test" blank enter "E4>100000" tab in "value if true" blank enter "YES" tab in "value if false" blank enter "NO" tab
In cell C13 enter a formula using the IFERROR function that uses the existing VLOOKUP function in cell C13 as the value function argument, and "Valid Job Title" as the customized error message for the value_if_error function argument.
click cell c13 in the formula bar type IFERROR( after the = at the end type ,"Invalid Job Title") press enter
Apply the currency number format to the vertical (value) axis. Use default setting for decimal places, symbol, format code, and links.
click chart right click the left axis drop down to format axis select number in category dropdown to currency exit the box
For the pie chart data labels edit the label options to display percentage format first, followed by removal of the value labels, at the inside end position, and then close the pane.
click graph select green plus in top right corner find black arrow on the far right end across form the middle option drop down to "more options" check the "percentage" box (in the middle) uncheck the "values" box a few above percentage scroll down in box fill in the "inside end" bubble at the very bottom exit out of box
Add a data table to the bottom of the chart
click graph click green arrow in the top right corner click data table in middle to bottom
remove the San Diego data series (range I7:L7) from the clustered column chart
click graph go to design in the same row as file and home click "select data" on the right side in data click the san diego and click "X Remove" in the top click ok
move the stacked column chart to a chart sheet; accept the default chart sheet name
click graph go to design in the same row as file and home go to "move chart" farthest to the right fill in bubble for "new sheet" click ok
in cell D18 create a formula using the MIN function to calculate the maximum value in range D4:D17
click in cell D18 find autosum dropdown in top right in editing category drop down to "min" click enter
change the pie chart to a clustered column chart. choose the option with all blue data bars.
click pie chart graph go to design in the same row as file and home click "change chart type" right side drop down to column click ok
move the clustered column chart so that the upper left corner of the chart border is over cell F1
drag so that the top left corner is aligned with F1
Save the current workbook to the financials folder (Documents>Financials). do not change the workbook name
file save as browse double click financials folder icon open save
Insert column to the left of column D
select entire D column (click the D) right click TWO fingers select insert
add the sheet name header element to the center header section and add the text "Last Viewed_" followed by the current date header element to the right header section. Click A4 cell to deselect the header when finished.
in the bottom right corner there are three different types of boxes with different designs inside... select the middle box click the section for add header at top of sheet go to design tab in same row as file and home at top of page click "name sheet" in middle/left in header & footer elements in the next cell type "Last Viewed_" go up to current date in header & footer elements on top left after clicking this select a different cell
enter formula using arithmetic operators and parentheses
same as above use ( ) =(b1+b2+b3)*12
copy the values and number formatting but not the underlying formulas
select all cells find two pages icon (between paintbrush and scissors) in top left corner click in new cell click paste in the top left corner underneath clipboard drop down to clipboard with %2 (second row middle)
in cell B12 use the Quick Analysis tool to create a formula that uses the SUM function to total the values in the range B3:B11
select all cells B3:B11 a small white button should pop up at the bottom right of selected cells "quick analysis" click "totals" click "sum"
Center contents
select cell click center in alignment in top left/middle
use the format painter to apply the formatting from cell B11
select cell B11 find paintbrush in top left in clipboard click and drag cells downB12:B19
apply the currency number format using the $ symbol and showing two decimal places
select cells go to accounting drop down in number in middle drop down to currency
clear conditional formatting rule in range...
select cells go to conditional formatting in styles in middle drop down to "clear rules" drop down/over to "clear rules in selected cells"
highlight the cells whose values is less then 20 using red fill
select cells go to conditional formatting in styles in middle select "highlight cells rules" drop down to "less than" type 50000 with (select) "yellow fill with dark yellow text" click ok
merge and center the contents
select cells and click merge & center in alignment in top left/middle
for values in range B3:B12 adjust the number of decimal places so that two decimal places are displayed
select cells in number in middle click <0 .00 x2
autofit so that cell content is visible
select entire D column (click the D) click format in cells in top right of page click "auto fit column width" in drop down
enter number using keyboard or keypad
use ' before number.
