Spreadsheets Task 4 Step by Step - PowerPoint Presentation

1. SpreadsheetsTask 4Step by Step7 July 2010Spreadsheets Task 041

2. Start with the raw materials7 July 2010Spreadsheets Task 042A plain-vanilla spreadsheet that needs some tidying up in look and function

3. Table Setup subtasks7 July 2010Spreadsheets Task 043But only do this in situations where you want exactly the same action for all the sheetsYou may need to select all sheets so that one formatting action serves for all sheets

4. Table Setup subtasks7 July 2010Spreadsheets Task 044Do this in situations where you want exactly the same action for all the selected sheetsYou may need instead to select only some of the sheets so that one formatting action serves for all selected sheets

5. Table Setup7 July 2010Spreadsheets Task 045For example: one of the setup subtasks calls for an action that might be different on individual sheetsIt might not work well with all sheets grouped because the titles spread across different columns on different sheets.They will need to be done sheet by sheet.Note: this cell formatting example is slightly different from what you are to do. In this example, titles are centered; in your task, titles are (with one exception) left indented.

6. Table Setup7 July 2010Spreadsheets Task 046But some of the requirement to tidy up the header rows might be able to be done with all sheets grouped because the formatting applies to the same cells on all sheets

7. Table Setup subtasks7 July 2010Spreadsheets Task 047But only do this in situations where you want exactly the same formatting in exactly the same cell on each sheet.You may be able to to select all sheets so that one formatting action serves for all sheets

8. So what would the workbook look like after the table setup subtasks have been done?Again, step by step7 July 2010Spreadsheets Task 048

9. Header Names7 July 2010Spreadsheets Task 049Cut and paste header names to fit in their own individual cells

10. Unmerging7 July 2010Spreadsheets Task 0410Be sure to place header data over the column to which it relates, no matter where it is

11. Format Header Cells7 July 2010Spreadsheets Task 0411You align cells by grouping the sheets if you wish. If you format the widest range of cells, you will surely get all the cells you need to format.

12. Format Header Cells7 July 2010Spreadsheets Task 0412But you will need to go sheet by sheet to make the look consistent (since all sheets don’t have the same number of header cells)

13. Some headers will take more formatting7 July 2010Spreadsheets Task 0413Make them stand out visuallyYou can either format the cells or use the quick styles tool in the Table Tools ribbon

14. Tidy up the tables7 July 2010Spreadsheets Task 0414Format appropriate cells as currency (this box points to an inappropriate range of cells)

15. Know the difference in numbers7 July 2010Spreadsheets Task 0415Format appropriate cells as accounting

16. Freeze panes7 July 2010Spreadsheets Task 0416Do it one sheet at a time

17. Insert new sheet7 July 2010Spreadsheets Task 0417

18. Rename sheets7 July 2010Spreadsheets Task 0418Right click on the tab

19. Modify new sheet7 July 2010Spreadsheets Task 0419Also copy a sheetAdd text & title

20. Copy an existing sheet7 July 2010Spreadsheets Task 0420Rename itPut the copy in the correct location

21. Data validation7 July 2010Spreadsheets Task 0421Modify the error alert to make it more meaningful

22. Hiding Columns7 July 2010Spreadsheets Task 0422Hide the appropriate columns.

23. Hiding Columns7 July 2010Spreadsheets Task 0423We could use hidden column data to subtotal, but we will keep the relevant data visible for that purpose

24. Comments7 July 2010Spreadsheets Task 0424Be certain that you are on the correct sheet

25. Comments7 July 2010Spreadsheets Task 0425One comment is visible and normal, the other is hidden and the text box is modified

26. Functions7 July 2010Spreadsheets Task 0426Three functions are at work here (in the cells shown in red only for this demonstration) and each of the functions are applied to a range of cells on another worksheetDon’t hesitate to use the help tool to find the right function to use

27. Formulas and formats7 July 2010Spreadsheets Task 0427Maybe something will change when I format the column appropriately.I did the formula for cell H5, but is this the answer I should see?

28. Formulas and formats7 July 2010Spreadsheets Task 0428After formatting the column, I need to use the grab handle to drag the formula from H5 down to all the cells in the column.Could I create a similar relative formula for Federal Executive Branch civilian employment per 1,000 population?

29. Sorting7 July 2010Spreadsheets Task 0429Sort the appropriate data, using the column headers as sort criteria

30. Sorting7 July 2010Spreadsheets Task 0430Sort the appropriate data, using the column headers as sort criteriaYou have two sorts to do and the second one has a primary and a secondary sort

31. Filtering7 July 2010Spreadsheets Task 0431First, you must apply the filter tool to the correct data.Then, you must decide what kind of filter to use and what criteria to apply to it.

32. Filtering7 July 2010Spreadsheets Task 0432Two filters are pretty straightforward custom filters

33. Filtering7 July 2010Spreadsheets Task 0433But for the third filter, we do a multilevel filter, the second part affecting the first.Could this have been done as a Boolean (AND – OR) question on a single set of figures instead?Or is it, in effect, a Boolean question applied to two values?

34. Subtotals7 July 2010Spreadsheets Task 0434Be sure to select the header rows along with the data rows when you apply the subtotal tool.

35. Subtotals7 July 2010Spreadsheets Task 0435Again, be sure to select the header rows along with the data rows when you apply the subtotal tool.Here the subtotaled data is minimized so that only the subtotals themselves are visible.

36. Chart 17 July 2010Spreadsheets Task 0436Be sure to explain the scales you are usingIf you label your chart axes adequately, you have no need for a legendChart titleDon’t forget this

37. Chart 27 July 2010Spreadsheets Task 0437After choosing a valid chart type, I modified the resultant chart by making the second data series display as a different chart type and decided against adding a second Y-axis because the two series are using the same scale. Chart title is a formatted text box added to the chartI chose to label the categories on the chart (using a legend) instead of on the axesThis annotation is a drawing object added to the chart

38. Now, let’s set up the pages for printingRememberWe want this to lay out nicely in print format7 July 2010Spreadsheets Task 0438To show you that the underlying data is not tied to the computer you use, the remaining images show the same data displayed on a different computer.

39. Page Setup7 July 2010Spreadsheets Task 0439You might have to do this part individually by page. You won’t necessarily have the same row or column to show on each worksheet.

40. Page SetupYou can do these things with all the pages selected, so that the formatting applies to them all.7 July 2010Spreadsheets Task 0440

41. And you’re done7 July 2010Spreadsheets Task 0441Note how many pages are in this formatted workbook. If you have a slightly different number of pages, you’re fine. If you have a very large difference in number of pages, you might ask yourself why?

42. Not that much to itThough it will take some time to doThis example was fairly minimally set up for printing. You may choose to do more with yours should you wish.7 July 2010Spreadsheets Task 0442