Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Excel Formatting and Functions, Exams of Educational Mathematics

University of WitwatersrandEducational Mathematics

Instructions on various excel formatting and function tasks, including clearing cell contents, adjusting row height, applying cell styles, creating tables, importing data, removing hyperlinks, clearing conditional formatting, formatting data ranges as tables, applying chart styles, inserting page breaks, copying formatting, calculating averages, adding data to charts, using autofilters, setting print areas, saving worksheets as webpages, wrapping text in headings, inserting columns, splitting worksheet views, freezing panes, formatting tables with banded rows, extracting text using functions, creating sparklines, moving charts to new sheets, applying number formats, switching chart axis, showing formulas, copying sparklines, adding total rows to tables, using if functions for overtime, converting names to uppercase, and creating 3d clustered column charts. A wide range of excel skills and features that could be useful for students in various academic subjects and courses.

Typology: Exams

2024/2025

Available from 10/17/2024

alex-david-34
alex-david-34 🇿🇦

4.5

(4)

5.6K documents

1 / 9

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
GMETRIX PRACTICE EXAM 2
Project 1 task 1 - 1. Above the worksheet to the left of the formula bar, click the Name Box down arrow.
2. Select Appheading2.
3. Cell range E1:F3 should be selected.
4. Right-click on the selected cells and select Clear Contents.
5. Click OK.
On the Downloads worksheet, adjust the height of row 27 to 78. - 1. On the Downloads worksheet, click
row 27 to select it.
2. On the Home tab, in the Cells group, click Format and select Row Height.
3. In the Row Height pop-up box, type 78.
4. Click OK.
Apply the cell style Light Blue, 40% - Accent 2 to cell A27. - 1. Select cell A27.
2. On the Home tab, in the Styles group, click the More dropdown.
3. In the Themed Cell Styles section, click Light Blue, 40% - Accent 2.
Create a table with headers from cell range A3:B24 by applying the Blue, Table Style Light 10. - 1. On the
Downloads worksheet, click anywhere within the cell range A3:B24.
2. On the Home tab, in the Styles group, click Format as Table.
3. In the section, select Blue, Table Style Light 10.
4. In the Format As Table pop-up window, do the following:
a. Confirm the data field contains =$A$3:$B$24.
Insert a Footer that displays today's date on the right, and then return to Normal view. - 1. On the Insert
tab, in the Text group, click Header & Footer.
2. In the Header & Footer Design tab, in the Navigation group, click Go to Footer.
3. Click the rightmost cell in the Footer.
pf3
pf4
pf5
pf8
pf9

Partial preview of the text

Download Excel Formatting and Functions and more Exams Educational Mathematics in PDF only on Docsity!

GMETRIX PRACTICE EXAM 2

Project 1 task 1 - 1. Above the worksheet to the left of the formula bar, click the Name Box down arrow.

  1. Select Appheading2.
  2. Cell range E1:F3 should be selected.
  3. Right-click on the selected cells and select Clear Contents.
  4. Click OK. On the Downloads worksheet, adjust the height of row 27 to 78. - 1. On the Downloads worksheet, click row 27 to select it.
  5. On the Home tab, in the Cells group, click Format and select Row Height.
  6. In the Row Height pop-up box, type 78.
  7. Click OK. Apply the cell style Light Blue, 40% - Accent 2 to cell A27. - 1. Select cell A27.
  8. On the Home tab, in the Styles group, click the More dropdown.
  9. In the Themed Cell Styles section, click Light Blue, 40% - Accent 2. Create a table with headers from cell range A3:B24 by applying the Blue, Table Style Light 10. - 1. On the Downloads worksheet, click anywhere within the cell range A3:B24.
  10. On the Home tab, in the Styles group, click Format as Table.
  11. In the section, select Blue, Table Style Light 10.
  12. In the Format As Table pop-up window, do the following: a. Confirm the data field contains =$A$3:$B$24. Insert a Footer that displays today's date on the right, and then return to Normal view. - 1. On the Insert tab, in the Text group, click Header & Footer.
  13. In the Header & Footer Design tab, in the Navigation group, click Go to Footer.
  14. Click the rightmost cell in the Footer.
  1. On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click Current Date.
  2. Click outside of the Footer cells. Import PetFoods.txt located in the GMetrixTemplates folder as a table on a new worksheet. - 1. On the Data tab, in the Get & Transform Data group, click From Text/CSV. (Hint: Comma-Delimited files and Tab-Delimited files are types of Text files.)
  3. In the Import Data pop-up window, browse to the GMetrixTemplates folder.
  4. Select the PetFoods.txt file and click the Import button.
  5. In the PetFoods.txt pop-up window, configure the following: File Origin: accept the default - 1252: Western European (Windows) Delimiter: Tab Data Type Detection: Accept the default - Base on first 200 rows
  6. Click the down arrow to the right of the Load button and select Load To...
  7. In the Import Data pop-up window, configure the folowing: Select how you want to view this data in your workbook: Table Where do you want to put the data? New Worksheet
  8. Click OK. On the Feed Inventory worksheet, remove the hyperlink functionality but leave the text in cell C27. - 1. On the Feed Inventory worksheet, select cell C27.
  9. On the Insert tab, in the Links group, click Hyperlink.
  10. In the Insert Hyperlink pop-up window, click Remove Link. Remove the conditional formatting from the Inventory column on on the Feed Inventory worksheet. - 1. On the Feed Inventory worksheet, click anywhere on the table.
  11. On the Home tab, in the Styles group, click Conditional Formatting.
  12. Select Clear Rules and click Clear Rules from This Table. On the Organic Feed worksheet, format the data range A3:F10 as a table that has headers. Apply the Dark Red, Table Style Medium 7 format. - 1. On the Organic Feed worksheet, select cell range A3:F10.

On the Profits worksheet, enter a formula in cell A29 that uses an Excel function to return the average costs of the cookie dough flavors based on values in the Cost column. - 1. On the Profits worksheet, select cell A29. (Hint: Always put your formula in the cell where you want the result to be displayed.)

  1. In the Formula Bar, type =AVERAGE, then press the tab key on your keyboard.
  2. To the left of the Formula Bar, click fx to open the Function Arguments wizard.
  3. In the Function Arguments wizard, configure the following: Number1: B4:B
  4. Click OK. (Hint: The formula in cell A29 should be =AVERAGE(B4:B26). The result displayed should be 1.73.) On the Profits worksheet, include the Total Profit data on the Flavor Expenses, Income, and Profit chart.
    1. On the Profits worksheet, click in the center of the chart to select it.
  1. Click the Chart Tools Design contextual tab.
  2. In the Data group, click Select Data.
  3. In the Select Data Source pop-up window, in the left window pane, click the box Total Profit to enable it.
  4. Click OK. (Hint: Total Profit should now be added to the legend representing the third column on the chart.) Filter the Profits worksheet to display only flavors with a Cost that is Above Average. - 1. On the Profits worksheet, click the autofilter down arrow on the Cost heading.
  5. Hover over Number Filters and click Above Average. Add Open to the Quick Access Toolbar. - 1. Above the ribbon, click the Customize Quick Access Toolbar down arrow.
  6. Click Open. Add the value New Rates to the Title property of the document. - 1. Click the File tab.
  7. Locate the list of Properties on the right side of the Info page.
  1. In the Title property field, type New Rates.
  2. Click the return arrow in the upper-left corner of the Info window to return to the worksheet. Set cells A1:F10 so that they will be the only cells that print. - 1. Select cell range A1:F10.
  3. On the Page Layout tab, in the Page Setup group, click Print Area and select Set Print Area. (Hint: You can check your print area setting under the File tab by selecting Print and viewing it in the Preview pane.) Save the Rooms worksheet as a webpage named Rent_Prices.htm to the Gmetrix Templates folder. - 1. Select the File tab.
  4. In the left pane, select Save As, and browse to the GMetrixTemplates folder.
  5. In the Save As pop-up window, configure the following: File name: Rent_Prices Save as type: Web Page (*.htm, *.html)
  6. Under Save: select Publish.
  7. In the Publish as Web Page dialog box, select All Contents of Rooms.
  8. Click Publish. (Hint: If prompted to keep using the format, click Yes.) On the Rooms worksheet, configure the heading row in the table (row 2) so that entries wider than the column wrap to multiple lines. - 1. On the Rooms worksheet, select row 2.
  9. On the Home tab, in the Alignment group, click Wrap Text. On the Sold worksheet, insert a new column before column A. - 1. On the Sold worksheet, select column A by clicking the A at the top of the column.
  10. Right-click on the column and select Insert. Split the view of the Sold worksheet so only rows 1 through 6 appear in the top pane and the remaining rows appear in the bottom pane. - 1. On the Sold worksheet, select row 7. (Hint: When splitting views, select the row below the last row you want to appear in the top pane.)
  1. Click OK.
  2. On the Sparkline Tools Design tab, in the Style group, click the More down arrow to open the Style gallery. Select Gold, Sparkline Style Accent 2, Darker 25%.
  3. In the lower-right corner of the cell, click-drag the Fill Handle from cell F11 through cell F14 to create a sparkline for each location. Using the Move Chart feature, move the pie chart on the Qtr 2 worksheet to its own chart sheet named Qtr 2 Chart. - 1. On the Qtr 2 worksheet, click anywhere on the pie chart to select it.
  4. On the Chart Tools Design contextual tab, in the Location group, click Move Chart.
  5. In the Move Chart pop-up window, select New sheet: and type the name Qtr 2 Chart.
  6. Click OK. On the Qtr 1 worksheet, apply a number format to display the numbers in columns B through E to two decimal places with the US Dollar symbol ($) left-aligned and the decimal points aligned. - 1. On the Qtr 1 worksheet, select columns B through E.
  7. On the Home tab, in the Number group, click the $ symbol (this will align the symbols). (Hint: Alternatively, click the dialog-box launcher in the lower-right corner of the Number group. On the Number tab, in the Category pane, select Accounting and configure Decimal places: 2 and Symbol: $. Then click OK.) In the Qtr 1 worksheet, change the configuration of the Qtr 1 Location Revenue chart so that it displays the months on the x-axis and the revenue on the y-axis. - 1. Click the Qtr 1 worksheet tab to select it.
  8. Click the chart to reveal the Chart Tools tabs.
  9. On the Chart Tools Design tab, in the Data group, click Switch Row/Column. Display all of the formulas on the Qtr 2 worksheet. - 1. On the Qtr 2 worksheet, select the Formulas tab.
  10. On the Formulas tab, in the Formula Auditing group, click Show Formulas. On the Average Call Time worksheet, use an Excel feature to copy the sparkline into all the vacant cells of the Trend column. - 1. On the Average Call Time worksheet, select cell G4.
  11. In the lower-right corner of cell G4, click-drag the fill handle down the Trend column beginning with cell G4 and ending with cell G16.

On the Employee Hours worksheet, add a row to the table that automatically calculates the total hours worked by all employees. - 1. On the Employee Hours worksheet, click on any cell in the table to select the table.

  1. On the Table Tools Design tab, in the Table Style Options group, click the Total Row to enable it. Add a function to the Overtime column in cell H4 on the Employee Hours worksheet that will display the word Yes if the value in cell J4 is higher than 40. Otherwise, display the word No. - 1. On the Employee Hours worksheet, click cell H4.
  2. In the Formula Bar, type =IF, then press the tab key on your keyboard.
  3. To the left of the Formula Bar, click fx to open the Function Arguments wizard.
  4. In the Function Arguments wizard, configure the following: Logical_test: J4> Value_if_true: Yes Value_if_false: No
  5. Click OK. In cell I4 of the Employee Hours sheet, use a function to copy the name from cell A4, and format the name so that all letters are uppercase. - 1. On the Employee Hours worksheet, select cell I4. (Hint: Always put your formula in the cell where you want the result displayed.)
  6. In the Formula Bar, type =UPPER, then press the tab key on your keyboard.
  7. To the left of the Formula Bar, click fx to open the Function Arguments wizard.
  8. In the Function Arguments wizard, configure the following: Text: A
  9. Click OK. (Hint: The result displayed should be EMILIO.) On the Average Call Time worksheet, create a 3-D Clustered Column chart that shows only the Call Time for Friday by each Salesperson. Position the new chart to the right of the table, and change the colors of the chart to Colorful Palette 4. - 1. On the Average Call Time worksheet, click the Insert tab.
  10. In the Charts group, click the Insert Column Chart icon and select 3-D Clustered Column.