Microsoft Excel 2007 Basics

Microsoft Excel 2007 has a completely redesigned user interface.  The standard menus along the top have been removed and replaced with a series of toolbars Microsoft calls “The Ribbon.”  These tool bars are changed using tabs at the top and try to automatically adjust themselves to the content you are working with.  If you select an image it will automatically switch to the picture tools.  The new layout seems to offer easier access to most of the features of Excel allowing for more complex documents to be created quicker.

The Office Logo

The first thing most people will need to relearn is where to go to open a document, create a new one, save your document and print.  Clicking the Office logo at the top left of the screen will provide most of the items formerly found under the file menu including those listed above.   Beside the logo you also will find a disk icon to save your document as well as the undo and redo buttons.  More buttons can be added to this “Quick Access Toolbar” through the Excel options mentioned in the next section.

As you can see here the Office logo opens up listing your options for new, open etc. and also contains a list of your recent documents for quick opening.  Any of the items listed with an arrow beside them will replace the recent documents on the right with the options associated with the menu item.  You should also see at the bottom right of this menu a button for exiting Excel and changing Excel’s options.

Save as

The save as option will provide you with the most common file formats to save your document in.  The common ones are Excel Workbook, Excel Macro-enabled Workbook, Excel Binary Workbook, and Excel 97-2003 Workbook.  The last one is the option most people should be using currently, especially if they wish to share documents with others who do not have the new version of Office.

Print

Here you can choose from Print, Quick Print and Print Preview.  Print brings up the standard print dialog box, quick print will print one copy without any dialog box coming up and print preview will bring up the print preview screen.

Prepare

The prepare menu’s most common options are Properties, Inspect Document, Mark as Final and Run Compatibility Checker.  The properties option allows you to setup metadata for the document like Author, Title, keywords, comments and others.  Inspect Document will scan the document for any hidden data like comments and annotations, and any hidden collaboration data.  This is very useful when making a previously private document public.  It will help you find any comments or changes made previously that should not be made public.  Mark as Final will mark the document as a final copy and make it read only so changes cannot be made.  The last common item, Run Compatibility Checker will scan the document for new features that were used and show you these.  This is recommended if you where creating a document in the new file format but now need to convert it back to the Office 2003 format to share with someone who does not have Office 2007.

The Home Tab

The home tab the basic formatting tools found in Excel 2007.  You will find seven sections, Clipboard, Font, Alignment, Number, Styles, Cells  and Editing.  Clicking the down arrow beside any of the icons here will drop down more options for that tool.  Each section also contains an arrow in the bottom right corner which will open a window containing the options found in that section.

Clipboard

The Clipboard allows you to cut, copy, paste and copy formatting from one place to another.  

 

Font

The font section of the ribbon provides a section to handle the basic text formatting.  Items such as bold, underline, strikethrough, highlight and font type can be changed here.  Some items from this section and some items from the number section are also available by right clicking a cell.  This saves having to move your cursor all the way to the top of the screen for some common formatting items.

 

Alignment

The alignment section provides icons to justify, Vertical alignment, indents, text wrap, merge cells and centre text and text orientation.

Number

The number section provides the options for formatting numbers.  You can choose the type of number, like date, currency, percentage, fraction or general.  You can also increase or decrease the number of decimal places shown.

Styles

The styles section allows you to quickly change the formatting of a section of cells by choosing one of the predefined styles.  You can choose different types of conditional formatting, table formatting or cell styles.  These are used to change the visual appearance of a section to quickly show what is being displayed in an area.

Cells

This section allows you to insert of delete cells, rows, columns, or sheets.  You can also format the height or width of columns and rows, hide or unhide elements, organize or protect cells and sheets.  There are a lot of features in this section under the format option. 

Editing

The editing section gives you options for inserting functions, filling formulas across cells, clearing formatting and formulas, sorting and finding.  Again, there is a large number of options in this area.  These options are also included in the image to left.

Insert Tab

The insert tab has five sections for inserting most types of objects.  The sections are tables, illustrations, charts, links, text and symbols. 

Tables

The tables section has an option for pivot tables and charts and tables.  You can select an area and turn it into a formatted table or pivot table with these options.  Once you have created your table there is another tab which becomes available to work with the table design.  This tab is shown below.

Table Design

 

Illustrations

The Illustrations section allows you to insert pictures, clipart, shapes, and SmartArt.  After inserting or selecting a picture you are provided with a new toolbar along the top shown here.   

This toolbar gives you the ability to change the brightness, contrast, shape, position, text wrapping and other options for the picture.  Clicking off the picture or on one of the other tabs will take you back to the standard toolbars.  The Shapes option of the Illustrations section allows you to insert lines, arrows, boxes, basic flowchart shapes and a number of others.  The SmartArt option provides features like org charts, flow charts, illustrated lists and processes.  The Chart option is similar to Excel 2003 but it offers more options for your charts.

Charts

The charts section provides drop down menus to insert different types of column, line, pie, bar, area, scatter and other types of charts.  Once you create a chart you will get three additional tabs to work with your chart.  They are shown below.

 inserting hyperlinks to your spreadsheet.

Text

Options in the text section include text box, header and footer, WordArt and a number of predefined text blocks like a signature line and symbols.  There are a lot of option in here to setup on your own or you can use Excel’s predefined options.

Page Layout Tab

The page layout tab has five sections, Themes, Page Setup, Scale to Fit, Sheet Options, and Arrange. 

Themes

The themes section provides a quick way to format your document.  By choosing a theme you will have a set colour scheme, font combinations, and effects.  You can choose one of the provided themes, modify any provided theme or create your own.  You can also go online in this section and browse Microsoft.com for additional themes.  Be aware that changing your theme after creating a document may require you to reformat some items as themes also include some layout options.

Page Setup

Page setup provides you with the tools to change margins, size, orientation, columns, breaks, backgrounds and add print titles to the document. 

Scale To Fit

This section allows you to scale to fit your document onto a certain number of pages or to scale it to a certain percentage of it’s current size.

Sheet Options

This section gives you checkboxes to view or print headings and gridlines.

Arrange

The arrange section is also found in the image toolbar when an image is selected.  Here you can change an images position, the alignment, grouping and rotation or the image.

Formulas Tab

The formulas tab contains four sections, function library, defined names, formula auditing and calculation.

Function Library

This section gives you access to the large number of predefined Excel functions.  There are Auto Sum functions, financial, logical, text, date and time, lookup and reference, math and trig, and additional functions.  It also provides a section for recently used functions so you can get back to the ones that you use the most quickly.

Defined Names

The defined names section allows you to create names or variables to be referenced in other areas.  This would be like assigning “taxrate” to cell b2 so in a formula you can easily see what you were referencing when you look at a formula months after creating it.  The formula =A10*taxrate means more later than =a10*b2

Formula Auditing

This section provides tools to manage formulas to ensure that they are correct.  You can use the tools here to find out what cells your formula depends on, what cells depend on the current cell, and show the formula in a cell instead of the resulting value.   There is also a tool for error checking which will look for common errors in  formulas.

Calculation

Here you can turn on or off automatic calculation from formulas.  If you turn off automatic calculations you can use this area to calculate the current cell or the whole sheet.

Data Tab

The data tab contains five sections, get external data, connections, sort and filter, data tools, and outline.

Get External Data

This is a single drop down item which allows you to refresh data from an external source.  You can pull in data from Access, the Internet, text files or from other external sources. 

Connections

Here is where you manage any connections to outside sources.  The setup done here is what allows you to pull data from the external sources listed above.

Sort & Filter

Here you can sort or filter your data based on criteria you specify.  You can use simple alphabetical sorting or create more complex filters to manipulate your data.

Data Tools

With the data tools section you can take text and turn it into columns, remove duplicates, check your data to make sure it is valid based on different criteria.  Are they all whole numbers?  Are they all properly formatted dates?  You can also consolidate data or do “What if” analysis where you can check you data against different scenarios.

Outline

The final section in the data tab provides tools to group or ungroup rows or columns and obtain subtotals for grouped items. 

Review Tab

The review tab offers three sections which include proofing, comments, and changes.

Proofing

The proofing section provides the standard spelling and grammar check, a thesaurus, and research tools that include MSN search and Microsoft Encarta encyclopedia.  You will also find translation tools to help with single words or the whole document.  The translation of the entire document is done by an online service called Wordlingo.

Comments

The comments section allows you to add comments to a document for easier collaboration.  You can cycle through the comments to find out what notes you left for yourself or others and you can delete a comment that was made when it is no longer relevant.

Changes

The changes section allows you to protect a sheet or the entire workbook, you can also share a workbook.  Some of the sharing features are only available to users on a Windows domain.  This authenticates the users against the domain for access so this will not work for many people.

View Tab

The view tab offers five sections which include workbook views, show/hide, zoom, window and macros.

Workbook Views

The workbook views section switches your display between normal view, page layout, full screen and page break preview.  The page layout will show you what will be on each page and provide a way to add headers and footers to each page.  The page break preview will show you a scaled version of your spreadsheet with dotted lines showing where the page breaks will be.  There is also a way to work with custom views here.

Show/Hide

The show/hide section will toggle certain tools on or off the screen including rulers, gridlines, message bar, formula bar and headings.  The rulers will show along the top and left side of the screen.  Gridlines will show the grids around each cell.  They will be visible on screen but don’t print. 

Zoom

The zoom section provides tools to zoom into or out of the document.   You can choose your own zoom factor or use the predefined zoom factor of 100% or you can select a section and zoom so it fills your screen.

Window

The window section allows you to create a new window, arrange your windows one on top of the other or split your window so the same document is viewed in two screens one on top of the other.  You can also hid and unhide a window.  Once you have arranged your windows the way you like them you can save the workspace so you can open to this setup when you need to work on these items again.  This is very handy for setups where you need to work on multiple documents at once and you have to do this a lot.  You can also choose which window to work on through the switch windows drop down menu.

Macros

The macros section provides the tools required to work with and create basic macros.  You can view existing macros or record your own.  Choose record macro from the drop down and then perform the functions you do often, like change the page layout, and style of the document.  Once you have done those tasks then stop recording.  You will be able to use that macro over again to shorten the steps you need to take every time you need to perform that set of tasks.