Microsoft Excel Basics

1. Microsoft Excel Basics Pat Berastegui-Egen SHARE 93 - Session 7252 pregen@egenconsulting.com

2. Excel Components • Title Bar • Shows the active document name and can minimize, maximize, or close the application window. • Menu Bar • Contains menus with commands that can be utilized in Excel. It can also minimize, maximize or/ close the current file in thedocument window. • Toolbars • Contains buttons that quickly access the most utilized menu commands. • Worksheet • The worksheet (or spreadsheet) is used to store and manipulate data. • Status Bar • The status bar indicates the current status of the document. • Active Cell • The active cell has a bold black border around it. It indicates the current cell that is selected and that information can be typed into that cell. • Reference Area • Makes a reference to the active cell (A1), and identifies a selected object. • Formula Bar • Displays the current data that is being entered or edited in the active cell.

3. Formatting Cells • Entering Text & Numbers • To enter text or numbers, select the cell where information is to be entered, then type in the characters using the keyboard and press enter. • Column/Row Size • To re-size columns or rows, drag the line in-between the column or row headings to the desired size, or double click on the line in the column or row headings. • Selecting Cells • To select (or highlight) cells, drag the mouse button from the beginning cell to the end cell. To select an entire row or column, click on the row or column heading. • Alignment • The default alignment for text is left and the default alignment for a number is to the right. To change a cell's alignment, select it and choose an alignment button on the toolbar. • Text Font, Style, & Size • To format the text font (Geneva), style (Bold), or size (12), select the cells in the spreadsheet, choose the Format menu, and choose the Font option. • Numbers • To format numbers, choose the Format menu and choose the Numbers option. Then choose the appropriate number format from the dialog window. • Formulas • To start a formula in a cell, type the equal sign, then type the cell references with the calculation symbols in-between each reference (=A5*B5+D5). To sum numbers in a row or column use the sum expression (=sum(a1:a19). To copy a similar formula from one cell to another cell, select the cell with the formula and choose the Copy button from the toolbar. Then select the new cell to apply the formula and choose the Paste button.

4. Formula examples

5. Types of formulas

6. Excel helps build a formula

7. The result

8. Charts and Graphs • Chart • A chart is a graphical representation of worksheet data. Charts can make complex data easy to understand. • Starting A Chart • To place a chart on the worksheet, first select the data for the chart, then click the Chart button. Once the marquee appears around the data, drag the mouse over the area where the chart will be placed the chart, and click the Next button in the dialog window that prompts you for information about the chart. • Default Chart • Excel automatically creates a column chart by default. Each column in the chart represents a cell on the worksheet and the value of the cell determines the column height. • Category Labels • The category labels are displayed according to whether more rows, or more columns were been selected in the chart data. • Data Series • A related group of values that are represented by the same color or pattern. • Adding Legends • To add a legend to a chart, double click on the chart to make it active, then select the Insert menu and choose the Legend option. • Chart Formats • To change a chart format, double click on the chart to make it active, then select the Format menu and choose a Chart Type option (Pie, Bar, Column, Line).

9. A chart of our example data

10. Charts with graphics

11. Data Forms • Data Form • A data form is a collection of information (records), stored in an organized form. • Record • A record is a collection of related information (fields) on single page of a data form. Each row or column in the spreadsheet represents a record in the database. • Field • Each item of information in a record is a called a field. • Field Name • The name of each column or row heading, indicates the name of a field for each record. • Defining the Data Form • Select a cells that will be included in the data form, then select the Data menu and choose the Form option. • Adding, Deleting, Searching • To add, delete, or search for information in the defined data form, select the Find Prev, or Find Next option in the data form. • Saving Documents • To save a file, choose the File menu Save As... option. Select the folder or disk to save the file in. Name the document, then choose Save button. • Opening Documents • To open a document, select the File menu and choose Open option. Select the folder where the document is located, then select the document and press the Open button.

12. DATA drop down menu

13. Example of a data form

14. Some helpful Tips • 1 - Centering Across Columns • One of the worst additions to Excel 97 is the new way in which the 'centering across columns' feature icon works. This new feature 'merges’ all the cells that the user chooses to center across, not allowing for column insertion or column resizing while these cells are merged. You can still however center across columns the traditional way by going into the 'Format' menu and choosing 'Cells'. Under ALIGNMENT, click the Horizontal drop-down menu and choose the 'CENTER ACROSS COLUMNS' option. • 2 - Inserting Images • Inserting images is much easier with Excel 97 over its predecessor, with the added ability to insert images of almost any file type. Simply go the Insert menu and choose IMAGE. Then choose FROM FILE. Finally, browse through your images on your computer until you find the file you want and then select it. The image can then be freely positioned and/or resized anywhere on your spreadsheet. • 3 - Paste Special • When copying and pasting data from one cell to another, you do not have to settle for pasting the EXACT same format as in the master cell. By right clicking on the cell before you paste, choose the PASTE SPECIAL option on the pop-up menu (this can also be achieved via the editing toolbar). This allows the user to paste selected elements from the area to be copied such as formulas or font type. This can also be applied to charts, allowing the user to arrange data in the chart to his/her specifications before the chart is altered.

15. 4 - Macros • Create a new worksheet and add a few BUTTONS via the forms toolbar. Then, create macros to link to specified sheets. Assign these macros to the individual buttons. Then rename the buttons accordingly. When your users need to go to REPORT 5 for example, they can now do so via the navigator page! • 5 - Autosave to save your work • How many times have you put hours into that spreadsheet or word document, only to have a co-worker trip over your power cable and instantly ruin your life. After tar and feathering your careless friend, the first thing you should do is to activate the AUTOSAVE add-in that comes with Microsoft Excel. Do this by going to the TOOLS menu and selecting ADD-INS. Then check off the AUTOSAVE option. When prompted, indicate that you would like to save every 15 minutes at least. It can be a little bit of a pain at first as you are asked to save 4 times every hour, but the next time your power goes out you'll be ready! • 6 - Remove those ugly gridlines and column headers. • Microsoft Excel 97's default is to display row and column headers (A,B,1,2,etc.) along with the gridlines of a spreadsheet. While this is necessary to have when creating a spreadsheet, by removing them once your work is done the worksheet looks cleaner and more of it is visible to the viewer. Simply go into the OPTIONS menu via the TOOLS drop down list on the toolbar. Then deselect 'Row and Column Headers' and 'Gridlines' in the VIEW tab - that's it!

16. 7 - Check out these great books on Excel 97. • The following books are by far two of the best references on Excel 97. The first, SMARTSTART: EXCEL 97 is great for users who are new to Microsoft Excel. It is easy to understand and it far superior to any Excel 97 manual. The second, USING EXCEL 97: SPECIAL EDITION is for the more advanced Excel 97 user. This book includes everything there is to know about MS Excel 97 and even showed the Excel pros at Aurora Business Services a thing or two. They can be viewed and even securely purchased online via the above link at Barnes and Noble's cool online site. • 8 - Hyperlinks are a nice touch. • Inserting hyperlinks is extremely easy in Microsoft Excel 97(but be prepared to be worshiped by your coworkers after showing them this). Simply click on the INSERT HYPERLINK button on the standard toolbar, indicated by picture of the globe with a link of chain attached. You can hyperlink to the Internet, or even to specific locations in your workbook! Even images in your workbook can be used as hyperlinks. Simply highlight the image before clicking on the INSERT HYPERLINK button

17. What’s new in Office 2000 • Full HTML support • Save in HTML and re-open in Excel • Publish a spreadsheet to the Web • Drag and drop from a browser into Excel • Improved Pivot tables • PivotChart - link charts to Pivot tables • Web Query Wizard • Refreshable Text import • Support for larger databases, OLAP, ADO • New cursors - change depending upon actions • Euro currency and Y2K support