Harness the Power of Excel 2007 Part 1: Sheet Setups

1. Harness the Power of Excel 2007 Part 1:Sheet Setups Lynn Mann July 17, 2008 For audio call Toll Free 1-888-886-3951 and use PIN/code 907705

2. Housekeeping • Maximize your CCC Confer window. • Mute your phone (*6) if you have visitors or noise in your office. • Please note phone audio may be in presenter-only mode. • Ask questions over the phone when the presenter prompts. • Ask questions throughout presentation via the chat window. • Turn on or off Closed Captioning by clicking on the icon. • Save the presentation or chat by clicking on the icon.

3. Agenda Sequencing using AutoFill, Custom Lists Creating templates Managing Formatting: Conditional Formatting: Highlight Cells, Icon Sets, Data Bars Managing Viewing: Freeze Panes, Split Screen Managing Printing: Page Breaks, Set Print Area, Repeating Rows/Columns Managing Data: Sort and Filter, Importing and Exporting

4. Sequencing using AutoFill Using AutoFill saves valuable time • Starting value(s) • Highlight cell(s) • Move mouse to bottom right corner, click and hold pulling over cells you want to fill with the sequence

5. Custom Lists • Click on the Office Button • Click on Excel Options • Select Popular on the menu list • Click on Edit Custom Lists • Type your custom list separated by commas or enter, click Add • Select cells to create list, click Add

6. Creating Templates If you work with a design that you reuse then create a template to be able to use without modifying or possibly corrupting an existing file. • Beginning with an existing file, click on the Office Button , either place your cursor over Save As (then click on Other Formats) or click on Save As. Select either Excel Template (.xltx) or Excel Macro-Enabled Template (.xlmx) depending on if your template includes macros – do not change the default template location. After you have renamed/saved the file, make modifications to remove any specific information. Leave all formatting, formulas, labels, and macros. Save the completed template. • Beginning with a blank spreadsheet, create all formatting, labels/headings, formulas, and macros. Save the file using same directions as above.

7. Managing Formatting: Conditional Formatting Conditional formatting is useful to analyze numerical or textual data visually • On the Home tab, in the Styles group, click Conditional Formatting icon • You’ll have several options to choose depending on how you want to display your conditional formatting: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scheme, and Icon Sets Let’s run through a few of these choices.

8. Managing Formatting: Highlight Cells Rules Highlighting cells with specific data allows analysis of data visually and quickly. • On the Conditional Formatting menu, place the cursor on Highlight Cells Rules, a submenu will appear. • Select the rule which you want to apply or select More Rules... • In the dialog box, select the test value and the formatting.

9. Managing Formatting: Icon Sets Icon Sets are fun visual aids to interpolate data. • On the Conditional Formatting menu, place your cursor on Icon Set, a submenu will appear. • If the default settings for the icon division (when the icon changes) is acceptable then click on the Icon Set you want to use. • If the icon divisions need to be customized, on the submenu move your cursor to More Rules… • Select the Rule Type • Edit the Rule Description • Note: Feature not available in previous versions of Excel

10. Managing Formatting: Data Bars To use Data Bars, the steps are the same as Icon Sets accept select Data Bars on the Conditional Formatting menu. • Select Data Bars • Select color • Modify rules if necessary • Note: Feature not available in previous versions of Excel

11. Managing Viewing Many times data flows off the viewing area but when scrolling to see the extended data we can’t see data that helps the visual data have meaning such as column and row headings. Freezing Panes and Splitting the screen can assist with viewing data. Note: Be sure Workbook View (on the View tab) in set to Normal otherwise you will not have these features.

12. Managing Viewing: Freeze Panes Freezing Panes allows the user the scroll through the data yet hold some of the data frozen in the viewing area. • To freeze either the first row or column then on the View tab, in the Window group, click on Freeze Panes icon • Select either Freeze First Row or Freeze First Column • To freeze a different area then activate the cell where you want the above rows and the columns to the left to freeze, then select Freeze Panes on the Freeze Panes menu.

13. Managing Viewing: Split Screen Sometimes you’ll need to leave some data on your screen and view other data in another portion of your workbook. Splitting the screen will allow you to do this. • On the View tab, in the Windows group click on Split icon • You’ll see your viewing screen split into four parts. • Move the split bar to the desired placement by clicking and dragging. • To remove the split either click on the Split button again to click and hold the split bar and drag it to the edge of the screen.

14. Managing Printing We have many options when printing our workbook: Orientation, Margins, Page Breaks, Print Area, Print Titles, etc. Let’s look at a couple of these options.

15. Managing Printing: Page Breaks • To insert page break, click on the column to insert the page break to the left or click on the row to insert the break above or click on a cell to insert multiple page breaks both above the cell and to the left. • To change/view current page breaks, click on View tab, in the Workbook View group, click Page Break Preview icon . Drag the breaks to the preferred position.

16. Managing Printing: Set Print Area • Highlight the area to print • On the Page Layout tab, in the Page Setup group click on Print Area . • Click on Set Print Area . • Click on the Office Button , click on Print choose printing options (i.e. number of copies, printer)

17. Managing Printing: Repeating Rows/Columns • On the Page Layout tab, in the Page Setup group, click on Print Titles . • Select which rows and/or column you want to repeat, either type or use the select button

18. Managing Data: Sorting • Highlight information to be sorted • On the Data tab, in the Sort & Filter group click either for ascending or for descending default sort. If you need to sort using more than one criteria click • In the Sort dialog box, select your options including if you need to sort by a custom list previously created

19. Managing Data: Filter Filtering allows us to view specific data without deleting data we want to keep but don’t want to view at the moment • Highlight the range of data, on the Data tab, in the Sort & Filter group, click on Filter icon . Arrows appear next to each of the column headings. (Or convert your data to a table) • For each of the headings which you want to filter, click on the arrow and select only the data you want to view • To turn off filtering, either click on the arrows and then select the box next to (Select all) or click Filter icon to deselect filtering option.

20. Managing Data: Import There are several ways Excel can import data from another type of format into Excel’s spreadsheet format: from Microsoft Access, text file, the Internet (Web), SQL Server, Analysis Service, XML, Data Connection (OLEDB - Object Linking and Embedding, Database), Microsoft Query (ODBC - Open Database Connectivity). Let’s look at the most popular, text file and web.

21. Managing Data: Import using Text Files • On the Data tab, in the Get External Data group, click From Text icon . • Select the file you want to import, click Import. The Import Wizard will walk you through the steps to import the data correctly. • Wizard Step 1: Delimited or Fixed data type. The wizard will try to determine the type of your data but if it chooses incorrectly you can manually change the type. • Wizard Step 2: Move, Create, Delete column breaks. The wizard will try to determine the column breaks of your data but if it chooses incorrectly you can change the column breaks either moving, creating or deleting the breaks. • Wizard Step 3: Formatting of each column. You can change the formatting of each of the columns being imported here at the wizard phase or after the data has been imported. You can also decide to import the column or skip importing certain columns. • Final step: Location of the imported data either in the existing sheet (starting with a specific cell) or in a new sheet

22. Managing Data: Import using the Web • On the Data tab, in the Get External Data group, click on From Web icon . • Type in the URL of the website of the information you want to import. • Select the areas by clicking on the yellow arrows when selected there will be a green checkmark . • When all areas to be imported are selected click Import button. • To change the options of refresh rate, click on Connection then Properties.

23. Managing Data: Export • To export data is actually saving the data as an importable format usually either text or comma delimited. • Click on the Office Button , click on Save As, choose either Text (Tab delimited) .txt or CSV (Comma delimited) .csv format using the drop-down list.

24. Q&A Lynn Mann lmann@msjc.edu 951-487-3524

25. Evaluation Survey Link http://tinyurl.com/62t3x4

26. Thanks for Attending For upcoming desktop seminars and links to recently archived seminars, check the @ONE Web site at: http://www.cccone.org/seminars/index.php