MVP #36: Excel 2010 Menus And You

, , No Comments
Today's Excel special post is on menus. Helping you make sense of the Excel menus, using Excel 2010 for illustration. I chose Excel 2010 because it's extremely similar to Excel 2007, and understanding it will make Excel 2013 not hard to understand.

So here we go.

We've got Home menu, Insert menu, Page Layout menu, Formula menu, Data menu, Review menu and View menu. There is one more menu that is hidden by default: Developer menu. It won’t be discussed because it is used mainly for including a macro (program) in an Excel file.

The home menu is Excel’s most used menu. It has very straightforward sub-menus.

Clipboard: Allows you to copy, cut and paste in Excel
Font: Allows you to set font size, color, background color (fill) & turn on bold or italics or underline.
Alignment: Allows you to set the position of whatever you've typed (or copied) into Excel. It also allows you to set how it’s written: horizontal, vertical or slanting.
Number: Allows you to set how a number is shown in Excel: regular number, currency, scientific, percentage, fraction… 
Styles: Allows you to set the format of an Excel cell based on the data it holds (aka conditional formatting). It also allows you to convert a selection of cells to table, and to set quick formats for a cell.
Cells: Allows you to insert new cells, delete cells and change cell format.
Editing: Houses the very useful Sort and filter tools, Find & Select, Find & Replace, and also Autosum which helps you sum all numbers in a selection.

 The Insert menu houses some of Excel’s best tools.

Tables: Allows you to insert PivotTable, PivotChart and Table. Inserting a table in Excel allows for quick formatting, and better formulas (via named ranges). PivotTable and PivotChart has been explained in my previous posts.
Illustrations: Allows you to insert images and shapes.
Charts: Allows you to insert charts.
Sparklines: Allows you to insert charts that fit into one Excel cell. Makes some reports beautiful and easy to read.
Filter: Allows you to filter out field values you are not interested in.
Links: Allows you point a cell content to a website or an email address.
Text: Allows you to insert texts and objects (pretty much anything, including a PDF document)
Symbols: Allows you to type out equations and special symbols.

The Page Layout menu does just that: setting up your Excel document’s page look and for printing.

Themes: It’s not very useful; it sets the look of the Excel window itself. 
Page Setup: It allows you to set how the page comes out when printed. Most used are the Orientation (to set as Portrait or Landscape) and Print Area (to select on the cells you want to print).
Scale to Fit: It allows you to set how much is printed per page. Most frequent use is to force Excel to print on one page, or fit all the fields (columns) on one page width.
Sheet Options: You wouldn’t want to change the default. It allows you set whether Excel gridlines be printed or not, and headings too. Default is no/off (unticked).
Arrange: It lets you rearrange overlapping objects (shapes, images, textboxes…). Or align them.

The Formula menu gives you access to Excel’s built-in formulas.

Function Library: It has the formulas grouped by category. Once you have an idea of what you want to do, it helps you locate the formula to use. It’s good to look through it once in a while to have an idea of the out-of-the-box analysis Excel can do.
Defined Names: Lets you name a selection of cells. Can be very useful when analyzing a big database.
Formula Auditing: Allows you to check for errors in your formulas and see how your final result is being calculated.
Calculation: Allows you to set when the formulas in your Excel sheet are calculated: automatic (whenever a cell value changes) or manual (at first entry and when you force them to be recalculated).

The Data menu allows you to work with external data and do basic data formatting.

Get External Data: It allows you to import or link to an external data file (non-Excel file). You’ll use it whenever you have a data in text file and need it to work on in Excel.
Connections: Allows you to make changes to the connections/links to an external data file. Or force a refresh of the connections to capture changes made in the external data file since last connection.
Sort & Filter: Allows you to sort data and do some filtering too. Filter allows you to specify values to display.
Data Tools: Allows you do very basic data analysis. Especially removing duplicate entries, and splitting one field into several (text-to-column). Example is splitting full name into First name and Last name.
Outline: Allows you to group (and hide) several rows. Useful for large data reports with few categories; helps to group categories.

The Review menu is for spell checks, commenting and setting access restrictions.

Proofing: Allows you to carry out spell checks and word meaning checks.
Language: Allows you to translate the Excel file content from one language to another.
Comments: Allows you to include comments in an Excel sheet, view all comments at once or delete comments.
Changes: Allows you to set access restrictions and track changes to the Excel file. Also allows you to share the file.

The View menu allows you to change the window layout of the Excel document. It doesn’t change anything in the actual document, just the way it’s displayed.

Workbook views: Allows you to set how the workbook (Excel file) is displayed.
Show: Controls what not printing details are shown: Gridlines, Headings, Formula bar and Ruler. The one you’ll be interested most in is Gridlines. If you want your Excel sheet to look more like a Word file, untick the Gridlines. That’s what’s done to every Excel sheet you see that has no Gridlines.
Zoom: Does what it says: sets zoom.
Window: Allows you to freeze headers so that when you scroll they will never be out of view. And also allows you to split the Excel sheet display, so you can compare two different parts of the sheet.
Macros: Allows you to see the macros programmed in the Excel file (if there’s any macro in it)

And that's my easy to grasp explanation of Excel menus. Don't forget to contact me for Advanced Excel training, Corporate Excel training and Custom Excel Solutions/Programs.


Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.