1 / 14

Certiport Excel Practice

Certiport Excel Practice. Editing Sort/Filter/Find & Replace. In the Summary worksheet, sort the data in descending order by Order Number, and then in ascending order by Unit Price.

helmut
Download Presentation

Certiport Excel Practice

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Certiport Excel Practice

  2. EditingSort/Filter/Find & Replace • In the Summary worksheet, sort the data in descending order by Order Number, and then in ascending order by Unit Price. • Filter the table to show only records with a Unit Price less than 400 and that have at least 10,000 units on hand. • Use Find & Replace to find all instances of 11111 in the Unit Number column and replace them with 22222

  3. Image • Apply a correction to the image so that it is sharpened by 25 percent, and apply a Pencil Sketch Artistic Effect. • Remove background

  4. Header/Footer • Add a header using Owner, Page Number, and Current Date format. • Add a field to the right footer that displays sheet name.

  5. Styles • Modify the Title style to use 32 pts. (Accept all other default settings).

  6. Saving • Save the file as a CSV (Comma delimited).

  7. Formulas • Enter a formula that sums the values in the Quantity column by using an existing named range. • Edit the formula so that it correctly checks the values in the column. • Edit formula to automatically maintain the correct cell references when copied (absolute reference) • Insert a formula that displays the highest value in the report. • Insert a formula in cell A1 that totals the values in cell F15 on the other four worksheets.

  8. Charts • Scale the chart so that it is 75% of its height and width.

  9. Page Setup • Set the options so that only the table headings are repeated when the worksheet is printed. • Copy and move an entire worksheet to a new workbook.

  10. Cell Ranges • Edit the cell range Goals A so that it only includes the goal values for that employee. • Copy and Paste only values.

  11. Inspect Document • Check the document for personal information.

  12. Conditional Formatting • Apply blue gradient fill data bar conditional formatting to the total row for each employee. • Apply conditional formatting to the Price column so that values greater than 200 are formatted with a Red Fill with Dark Red Text and values less than 200 are formatted with a Green border (accept all other default settings).

  13. View • Create a custom view.

  14. SmartArt • Apply the Circle Arrow Process layout to the SmartArt graphic, change the style to Metallic Scene, and reverse the direction from Right to Left.

More Related