Excel 2007: User Interface

Excel 2007 User Interface

• The MS Office interface in Excel 2007 has been redesigned as a results-oriented user interface, improving the discoverability of features.
• The new user interface is named "Fluent" and is implemented in Word, Excel, PowerPoint, and Access.
• A notable component of Fluent is the Ribbon which makes commands easier to find on task-oriented tabs that contain logical groups of commands and features.
• Other components of the user interface are the MS Office Button, the Mini Toolbar, and the Quick Access Toolbar.
• Formula AutoComplete, Live Preview, and Gallery are new features that help with usage, although they are not controls.

Components of Fluent

• The MS Office Button replaces the File menu and is located at the top-left of the screen.
• Pressing Alt + F activates the MS Office Button, maintaining the shortcut from previous versions.
• The Ribbon replaces all other menus and toolbars and is a tab-based banner at the top of the window.
• Selecting a tab in The Ribbon displays corresponding options and icons.
• The Ribbon has seven default tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View which cover most user options.
• Contextual tabs, such as Developer, appear on the Ribbon as needed based on the object being used.
• Command icons on each Ribbon tab are grouped into blocks.
• Some command icons have a small link at the bottom to open a separate window with more options.
• Context-sensitive tabs appear on the Ribbon depending on the selected object.

Mini Toolbar

• The Mini Toolbar is a small toolbar that pops up when content is selected, or a right click occurs when dealing with multiple cells.
• The Mini Toolbar provides quick access to frequently used formatting options
• Font changes, bulleting, and numbering are options available in the Mini Tool Bar.

Formula AutoComplete

• Formula AutoComplete displays a list of functions matching the characters typed into a cell beginning with =.
• Formula AutoComplete shows a brief description of what the function does.
• Selecting a function shows the function's syntax, this reduces errors.

Quick Access Toolbar

• The Quick Access Toolbar is configurable
• The Quick Access Toolbar appears next to the Office Button at the top left.
• You can customize what appears on the Quick Access Toolbar.
• It provides easy one-click access to commonly used functions.
• The Quick Access Toolbar contains Save, Undo, and Redo options by default.

Live Preview User Interface

• Live Preview provides a preview of formatting options when hovering over them
• Live Preview removes the need to undo changes that the user is unsatisfied with.

Gallery Feature

• Gallery graphically displays the sample application of an effect
• Gallery makes it easier for the user to see the impact of effects.
• An example is displaying colors of a table rather than listing them.
• The Ribbon can be hidden by double-clicking any of the tabs.

Internal Changes

• Excel's computational limits have been significantly increased.
• Excel now supports 1,048,576 rows and 16,384 columns in a spreadsheet.
• 65536 rows and 256 columns were the old limits.
• Excel can use up to 4 GB of RAM, specifically using up to 2GB.
• Improved multi-threading is used to better utilize multicore CPUs.
• The number of characters per cell increased from 1,024 to 32,768.
• The number of unique colors increased from 56 to 4.3 billion.

File Types

• Office 2007 uses the Office XML format, which is XML-based and platform-independent.
• The new file format also uses ZIP compression.
• Microsoft reports a 75% reduction in size using the new file format.
• Excel by default saves workbooks in XLSX file format.
• The XLSX file format is not advisable for documents containing macros.
• XLTX format is used to store template spreadsheets which do not support macros.
• XLSM file format supports macros and is used for spreadsheets with macros.
• XSTM file format stores spreadsheet templates with macro code.
• XLSB file format is optimized for speed and uses unzipped, binary format.
• XLAM format is used to save files containing add-ins.
• For compatibility, the older XLS format can still be used.
• There will be a loss of customizations if XLS format is used.
• The user will see a warning with a list of losses by saving into the older format

Office Button Functionality

• Most options located under the Office Button are familiar to most users.
• Common actions such as Open, Save, Save As, and Print remain unchanged.
• The "Prepare" menu includes features, such as the Inspect Document option.
• Inspect Document is used to vet a document for private information
• Comments, annotations, and hidden text are examples of data that may be misused.
• The Compatibility Checker reveals features that will be lost using older versions of Office.
• The Encrypt Document option adds a password to a spreadsheet.
• Excel Options allows the configuration of Excel.
• Setting the default color and disabling Clear Type are some configuration options.
• Clear Type is a Microsoft technology that improves the appearance of text.
• The Mini Toolbar and Live Preview are also configurable here.

Ribbon Tabs Home

• The Home tab contains all the main formatting tools.
• Font formatting and paragraph formatting options are under the Home Tab.
• Copy/paste tools are available through the Clipboard Block.
• The Clipboard tool tracks the contents of the clipboard and can be launched by clicking on the bottom left corner of the block.
• The Font Block contains the usual font formatting tools and more options, and be accessed by clicking on the pop out link at the bottom left of the block.
• Alignment, Number, Styles, Cells, and Editing are all blocks contained within the Home Tab.
• Indentation and alignment are tools in the Alignment block.
• The Styles block offers a Conditional Formatting tool that will be discussed in detail later.
• The Cell Block allows you to insert, delete, and format cells, rows, columns and sheets.
• Editing block contains tools to find, replace, sort data, clear cell contents, etc.

Ribbon Tabs Insert

• This tab contains tools to insert different types of content in the spreadsheet categorized in blocks.
• The Table block creates Tables or Pivot Tables.
• The Illustrations Block offers tools to insert Pictures and Clipart.
• Smart Art Tool- contains pre-built diagrams such as flow charts, and hierarchies.
• Charts- contains tools to create charts such as Pie, Bar, etc.
• Hyperlinks block- allows insertion of links to other sheets in the spreadsheet and online documents.
• Text block- Includes the ability to insert Word Art, Headers and Footers, or content from Word or PowerPoint documents.

Page Layout Tab

• The Page Layout tab has page setup, scaling, and arrangement of spreadsheets.
• Common functions include Page Orientation, Margins, & Background under the Page Setup Block.
• The Scale to Fit and Sheet Option group different tools to modify the spreadsheet viewing options.
• Objects in the spreadsheet are modified in the Arrange Block

Formula Tab

• The Formula tab lets you manage different functions in the spreadsheet.
• All functions are listed by category in the Function Library block.
• You can Verify dependencies with the Formula Auditing block.
• Cell blocks can be created and managed with the Defined Name Block.
• Excel calculations can be controlled in the Calculation Block.

Data Tab

• Tools to organize, summarize, and analyze data are found in this tab.
• Get External Data block groups tools- imports from text files, XML files, or SQL databases.
• Connections to databases are controlled by using tools in the Connection Block.
• Data Tables can be managed in the Sort and Filter, Data Tools and Outline blocks

Review Tab

• The Review Tab has Proofing, Comments, and Changes Blocks
• The Proofing Block allows non-numerical data in the spreadsheet to be verified
• The Comments Block has tools to manage comments in the Spreadsheet
• The Changes Block enables tools to track changes to the spreadsheet as well write protection

View Tab

• This tab allows the user to change how the worksheet is viewed
• Workbook Views, Show/Hide, Zoom, and Window are all blocks that apply to this Tab
• Macros can be used to view or create new macros.

Developer Tab

• Tools for using Excel as an application to accept user input can be found here.
• The developer tab is hidden by default.
• Control Block, Code Block, and XML block are tools of the Developer Tab
• The Control Block- Accept user input
• Code Block- Manage macros.
• XML block- Import and manage XML data sources.

Smart Art Tools Tab

• Design and Format are the two tabs of the Smart Art Tools category.
• These appear on selecting or inserting Smart Art objects.
• Smart Art graphics can be formatted, created and modified with these tabs.

Table Tools Tab

• The Design Tab is a sub-category of the Table Tools tab.
• The Table Tool tab appears only if a Table is selected or created.
• You can change the table name and data range inside the Properties block .
• The Tools Block allows you to summarize data as a Pivot Table.
• The Table Style Options and Table Styles blocks allow various modifications to the tables appearance.

PivotTable Tools Tab

• The Pivot Table Tools tab has two sub-categories
• Available Options and Design are used to work with PivotTables.
• These Tabs are only visible when inserting or selecting a PivotTable.

Picture Tools Tab

• The Format tab is a subcategory of the Picture tools tab
• This tab appears after inserting or selecting a Picture.
• Adjust Block modifies the picture parameters such as contrast and brightness .
• You can alter the look of an image in Picture Styles block.

Chart Tools Tab

• The Chart Tool tab appears when inserting or selecting a Chart
• There are three subcategories that appear namely the Design, Layout, and Format tabs.
• Under these three tabs are tools to modify chart data ranges, types, or presentation.

Header and Footer Tools Tab

• Appears only when dealing with Headers and Footers and contains Design sub category.
• Values inserted as headers or footers include page number, date, time and can modify the location of header/footer

Drawing Tools Tab

• Appears formatting and rendering Word Art and shapes.
• Coloring and rendering, and sizing options can be found here.

Working with Excel 2007

• Double-click on cell or select the cell and press [F2] to change value.
• Select another cell after changes or press [Enter] to complete process.
• Start typing a function to use Formula AutoComplete which displays functions available.
• Function list scrolled with Up and Down keys.
• The highlighted function has the brief definition.
• Use the [Tab] key to insert the identified function.
• After insertion, pop-up syntax displays.
• Parameters deserving attention are displayed in bold, optional parameters in square brackets.
• Excel Help and takes user to page containing detailed description of the function occurs when clicking the function name.
• Nested and new functions can be used, with a second pop-up listing the available options.
• Pop-up will be active until final function is properly closed, with round brackets.
• Use "Save as Type" in Save As to save as Web page for browsers.
• Select relevant option on Single File Web Page format in same window.
• Save as .mht or .mhtml file.
• Table is marked and manipulated as a unit or subset of spreadsheet with data related.
• You can Sort and filter data when converting range of cells as a table.
• Format as Table also offers formatting options and inserts data manipulation controls.

General Formatting

• The Home tab has all the popular tools.
• Tools to change font family, font size, cell border, text alignment are in Font block.
• Live Preview is available for font color, font family, or size selections.
• Alignment block- change horizontal and vertical alignment and the indenting.
• A control to modify text orientation is also available that is easily applied.
• The "Merge and Center" control quickly merges adjoining cells or unmerges them.
• "Merge and Center" centers in one go, warns of data loss.
• "Merge Across" merges cells column-wise.
• "Merge Cells" merges all selected cells.
• "Unmerge Cells" undoes all merge operations.
• Number block controls cell content types like currency, text, number etc.
• Possible data types are listed in drop-down.
• Quick formatting, currency, inserting commas is available.

Cells Block

• The cells block controls Insert and Delete to insert and delete rows, columns, and spreadsheets
• You can manually or automatically adjust cell height and width
• You can hide rows, columns or sheets
• You can rename a sheet with the Rename Sheet Link
• A spreadsheet also can be locked to prevent unauthorized access.
• Link to Format Cells has additional formatting options.
• The one feature is one that shrinks text to fit inside a cell, accessed through Alignment Tab .

Formulas and Functions

• Formulas/Functions start with "=" to Excel.
• Excel calculates using operators: arithmetic, comparison, text, and reference.
• Each Operator uses a different level of precedence which dictates how it is calculated.
• Using parentheses ensures Excel calculates inside them first.
• Nested parentheses means Excel will bring and and suggest corrections if there is an error.

Cell Wise Formulas

• Addition : =A1+B1
• Subtraction: =A1-B1
• Multiplication: =A1*B1
• Division: =A1/B1
• Sum: =sum(a1:h1)
• Minimize: =min(a1:h1)
• Maximize: =max(a1:h1)
• Average: =average(a1:h1)
• Count: =count(a1:h1)
• Example for remarks formula: =IF(AND(A1>=0,A1<=19), "FAIL",IF(AND(A1>=20,A1<=39), "GRADE-C",IF(AND(A1>=40, A1<=59), "GRADE-B",IF(AND(A1>=60,A1<=79), "GRADE-A","EXCELLENT"))))

All About Cell References

• Calculations start with a cell reference in the 1,048,567 rows and 16,384 columns.
• Alphabetical columns and numbered rows give a unique reference.
• Use cell reference to refer to certain values.
• Reference can refer to single cell or range of cells in a worksheet or another sheet.
• You can calculate with the addition, subtraction, division, or multiplication for example.
• Using AutoSum button will make Excel total the list of preceding numbers .
• You will usually enter "=" sign first to inform Excel of formula.
• Either enter in directly into the cell or use formula bar ("fx") at the top
• Entering references shows cells in colors to show locations.
• Named Ranges are special custom names you assign.
• Especially useful for complex and large Spreadsheets where Cell references are easily confused.
• You can only name the range without using spaces.
• Use Named Ranges in Formula or Mathematics one at a time. EX SUM(SalesDiv1)+ SUM(salesDiv2)+ SUM(salesDiv3) instead of SUM(SalesDiv1+SalesDiv2+SalesDiv3).
• Alter the formulas using defined Name- Find the Cell where you make or change the formula- Press F3.

Referring to Another Worksheet

• Refer to another Worksheet by -Click where reference occurs, the type in the =, then switch worksheet and click in the required cell.
• The values/formulas can auto Update- by turning on calculation, click on Office Button, after that Excel Options, Formulas, and finally select Manual under the Calculation option. Use [F9] or- To calculate current Sheet Use shift+F9.

Filtering

• Filtering limit visibility depending on entered criteria.
• "Filter" command and limit view.
• Number Filter offers common filter above, below, or greater than.
• You can open custom filter and use wildcard characters such as ? and *.

Summarizing Data

• Information collected- Total, Max, Average, Number of records that are part of summary report. SubTotal Accomplishes Summarization.
• Select cell in arrange after sorting and click SubTotal Button-This is found on the outline block under the data tab . Will open a window listing summarizing options.
• Column which the value needs to be identified key under each change field.
• Other summarizing operations include sum, count, average, Maximum these also appear under the use function field . The columns needs an subtotal row Also can decide to display summary that is below or above info
• Result performs subtotal routine and interjects a row insert and carry result of chosen summary action Final grand total row insert at and of self range carrying summary figure for range.
• Group controls make its entrance when subtotal method is completed on lift margin.

Chart Group

• Consists about 11 types . to start you push insert+alt +N after you have inserted into the worksheets select range of data you intend to chart.
• From chart group one can choose create and choose the layout and style also sub type of the chart
• Column Chart- it highlights data to show over time variations often used.
• Insert the column- five charts display 2+D column, 3+ dc column, cylinder, cone, pyramid.
• Different colors highlight data series. Series can stack one another for relative representation of two or three.

Pie Chart

• Displays data of relative contributions. Six sub categories.
• In 2+ sub type first is simple pie displaying each contribution other is the exploded pie.
• +D two - one hold all data one exploded and has values in data slices.
• Placed emphasis on part of slice on chart through colors wide patterns and Labeling. Remember only +Values are excepted when using chart using Negative values will just give positive value.

Bar Chart

• Less than column + five bar chart +D bar cylinder cone and pyramid Each sub type in three different formats: bars values near and stacked values each another / stacked / 1+ percent chart, graph.

Area Chart

• Same to column chart. Except area filled is filled between areas. Magnitude for values are less change for long term Has two area+ graphs. +3D, +D there is data fill stacked with those values.

Scatter Chart

• Showing comparison about the info it not easily showed
• For example expand insurance or expend insurance. Chart is good for type. +axes charts numeric legend field. 5+ sub types show the connection use and variants. Types are smooth markers lines and the straight type.

Other Charts

• Apart from + charts it shows option where there is 5 chart times listed the type here separate one and only because they have specific use.

Stock Chart

• Shows the stock values to show how certain dates trade and open price
• Close of height prices There are types volume high close . Can display data show high low close.

Surface Charts

• Display series across each data point and color coded
• Four sub types Use that data 2 + chart series.

Doughnut Chart

• Similar to pie but data form can show what is on charts, can add into between if some of the information the what and how to
• Has two chart forms.

Bubble Chart.

• Similiar to XY where there can be chart types of 3 bubbles charts. Draw back there can be lots of bubbles chart can show over lap Can show bubbles style as +3D

Radar Chart

• Good for relative comparison Changes data by relation from center. Three sub types .marker field .chart