Gmetrix Excel Practice 1
In the June worksheet, enter a formula in cell B34 that will get the SUM of the cell range B4:B33. Copy the formula to columns C and D.
=SUM(B4:B33), click and drag lower right corner of the cell to fill C and D
In the Totals worksheet in column B, enter a cell reference to the matching Totals values from the June worksheet.
Fiction =June!B34 Non Fiction =June!C34 Reference =June!D34
Freeze the first column in the current spreadsheet.
Go to view tab, click freeze panes, freeze first column
Change the margins to the Narrow setting.
Page Layout, margins, narrow
Use Fill to copy the Sparkline from cell F33 to F8:F32.
click and drag the lower right corner of the cell to fill the range
Filter the data so that only the people with a position of Developer are displayed.
highlight range, sort and filter, filter, click the dropdown that now appears on position, make sure the developer box is the only one checked
In the June worksheet, apply Table Style Light 4 to the cells A3through D34.
Highlight cells, click format as table, click table style light 4 in the first row, click ok
Without using the average function, create a formula in the cell H4 that gets the SUM of the values in the cell E4 through G4 and divides the sum by three.
=SUM(E4:G4)/3
In the Source Data worksheet, set the print settings to repeat row 1 at the top.
Change worksheet, page layout, print titles, for rows to repeat at top, highlight row 1
Add the title Average to column H.
Select cell, type "average"
In the June worksheet, in cells E4:E33 use the IFformula to display a 1 if the values of the cells D4:D33 are greater than 2500, and a 0 if it is less than or equal to 2500.
Sorry
In the Source Data worksheet use find & select to find the cell that contains Data Validation, and clear the validation.
Switch to source data sheet, click find and select, data validation, then click data, data validation (in the data tools group, looks like two little boxes, one with a check and one with a stop symbol) click clear data validation on the bottom left, then click ok
Change the view to Page Layout.
View, Page Layout
Change the options on this worksheet so that Gridlines are visible.
View, click the gridline box
In cell range B16:F16 set the style of the cell with the following characteristics: Number Format = Currency, Font = Courier New, Fill Color = Yellow.
Just like follow the instructions you know
In the Product worksheet insert a LineSparkline into cell F33 that references the data in cells B33 through E33.
Select Cell, Insert, In the sparkline group choose line sparkline, highlight data range
In the Review worksheet, use the CONCATENATE function to display the contents of cell B4 and A4 separated by a space in cell J4.
Select cell, =CONCATENATE(B4, " ",A4)
Change the Chart title to Annual Sales.
Select chart title, name to Annual Sales
Change the chart in the Totals worksheet to the 3-D Pie type.
Select chart, go to design tab, change chart type, select 3-d pie
Add a Dark Blue border to the image.
Select image, format, picture border, dark blue (bottom most row of colors)
Insert the Basic Process SmartArt graphic below the data in this worksheet.
Insert, Illustrations, Smart Art, process, basic process (first choice)
In the Totals worksheet enter a formula in cell B2that displays the SUM of the Fiction12 named range.
=SUM(Fiction12)
Format cells A3:G33 as a table using Table Style Light 2.
Highlight range, format as table, table style light 2 (its in the first row)
In cell B18, enter a cell reference to cell A7.
=A7
In the June worksheet, in Cell E34, enter a formula that returns the AVERAGE value of all the numbers in the range B4:D33.
=AVERAGE(B4:D33)
In the Annual Sales worksheet, in cell B17 use the COUNTIF function to count the number of times that the values in cell range B6:B15 exceed 5000.
=COUNTIF(B6:B15,">5000")
Filter the data so that only rows with a value that is Above Average are displayed in the Fiction column.
Highlight range (ONLY THE NUMBERS), filter, click the dropdown arrow that appears in column b, number filters, above average
Create a hyperlink in the cell B12 that links to the file EXCEL07.xlsx.in the GMetrixTemplates folder.
Right click B12, link, choose excel07 from the files, click ok
Unmerge any merged cells in row 2 of the Review worksheet.
Right click cell, click unmerge (button next to text justification choices in the alignment group)
Hide column A.
Right click column, click hide
Apply Gradient Fill-Green Data Bar conditional formatting to the values in column E.
Select range, click conditional formatting, data bars, green option
Apply the style Heading 3 to the title Blackbread Books.
Click cell styles, then under titles and headings, choose heading 3
Change the margins to the Narrow setting.
Page layout, margins, narrow
In the Annual Sales worksheet, apply a Links to the title FusionTomo Inc. that links to the website www.fusiontomo.com.
Right click the title, press link, change it to the first link option from the options on the left, enter the website in the address bar at the bottom of the window, click ok
Change the format of the cell containing the company name FusionTomo Inc. to the cell style Title.
Select cell style, under headings and titles click "title"
In the Source Data worksheet Sort the Data alphabetically by CustomerID and then by Product.
Change sheet, sort and filter, custom sort, change sort by to customer, then click add level and change that sort by to product
In the Quarterly Sales worksheet, cell B9, enter a formula that consists of a cell reference to the Annual Sales worksheet, cell F16, multiplied by 1plus the growth value in the Quarterly Sales worksheet, cell B3.
Change worksheet, ='Annual Sales'!F16*(1+'Quarterly Sales'!B3)
In the Source Data worksheet Unhide any hidden rows
Choose sheet, click the bottom right facing arrow in between row 1 and column A, click format in the cell group, then go to hide and unhide, click unhide rows
In the June worksheet, use Autofill to extend the series starting in the cell A4 to the cell A33.
Click and drag the bottom right of the cell down to A33
Copy the formula in cell H4 to cell H5:H33.
Click and drag the lower right corner of the cell down to cover range
In the Annual Sales worksheet, use the Find & Selecttool to find the cell in the open spreadsheet that contains Conditional Formating and apply a Thick Outside Border.
Click find and select, click conditional formatting, click thick outside border from the fonts group
Change the view to the split screen view.
Click the view tab, then click split under the window group
Inspect the current workbook for hidden properties such as Author. Remove any of these properties that are found and return to the document.
File, info, inspect worksheet, choose the first option, click ok, then remove any properties that show up
Create a new worksheet based on the Expense Report sample template.
File, new, type "expense report" in the search bar, select Blue Expense Report
Save this spreadsheet as a PDF file named EXCEL05 in the GmetrixTemplates folder. Do not open the file after publishing.
File, save as, PDF (make sure it's named Excel05)
In the Annual Sales worksheet, insert a 2-D Clustered Column chart based on the data in cell range A5:E15. Include the column headings and position the chart below the table.
Highlight cell range, insert, chart, clustered column chart, move chart below table
Use the AutoSum tool to get the SUM of the values for each cell (B through F) in the Total row.
Highlight cells, click the formula button (looks kinda like a geometric E by the find and select button) click sum
In the Product worksheet, change the format of the numbers in cells B8:E33 so that no decimal places are showing.
Highlight range, and choose the remove decimal button (on the right) under the number group
Apply a conditional formatting to the values in columns E, F, and G. Apply a yellow fill to any cells with a value equal to 5.
Highlight range, click conditional formatting, highlight cell rules, equal to ,5, custom format, fill yellow
In the Annual Sales worksheet, Sort the data in the cell range A5:F15 by the Total column and order the values by largest to smallest.
Highlight range, click sort and filter, custom sort, change column to total and order to largest/smallest
In the June worksheet, define the name Fiction12for the cell range B4:B33.
Highlight range, formula, name manager, new, change name to Fiction12, change the scope to june, ok
In the Time Card worksheet, insert the image ftlogo.gif, from the GmetrixTemplates folder, at the top of this worksheet.
Insert, Illustrations, picture from computer, go to the gmetrix folder (you may have to search your windows drive), select the logo, press insert
Change the Theme to Office.
Page layout, theme, office
Apply the Built-in theme Ion to the current worksheet.
Page layout, themes, ion
Change the theme of the current worksheet to Ion.
Go to the page layout tab, click themes on the far left, select Ion
Merge & Align Right the content in cells A2 through D2 in the June worksheet.
Highlight cell group, click merge and center, then change the alignment to right
In the Annual Sales worksheet, copy cells A7:F7 and paste it into cells A19:F19
Just like do ya know
In Annual Sales worksheet, insert a column Sparkline in the cell G6 that references the data in the cells B6through E6.
select cell, go to insert tab, choose column sparkline from the sparkline group, highlight data range, press ok
