Excel 2010 - The Home tab

Microsoft Excel 2007 introduced a new menu structure called the Ribbon. All the commands are grouped under tabs. Each tab represent a step in creating a document. The Home tab represents the first step and is composed mostly of basic commands. This web page concentrates on explaining the commands under this tab.

You will find in the clipboard all the commands and options to cut, copy and paste objects. Since Office 2003, it can contain and manipulate many objects at the same time. It can also reproduce a presentation format of a cell with Format painter.

The Cut option allows you to move an object from one location to another. You must select an object, cut it so that you can paste it elsewhere.

The Copy command allows you to select the element of your choice, leave it at the same location and paste it to as many other places. For example, you can recopier one formula mathématique in many other cells to many places in your model. S'il s'agit to une cell or to un bloc of cells, the bordure of celui-ci will clignoter jusqu'à this that you passer to another opération différente of coller. For example, the clignotement of cells will s'arrêter dès that you go start to write in another cell.

The Paste command consists to placing the content of the cell, or range of cells, that you cut or copied into a new location. This option also offers many possibilities. They will be descripbed later in this text.

There is first the option Paste of base that will coller to the location of your choice the element copié or collé sans use to other options.

By pressing the second half of the Paste button, you will have access to all the possible options of this command.

This option allows you to copy only the formulas you cut or copied without taking into consideration the presentation format such as the font style, its size and its background color.

This option will paste the value of the cell instead of copying the formula. For example, the result of the formula =A1*A2 is 225. The Paste values option will place the value 225 instead of the formula.

This option will paste the formula, the value and other presentation elements except the border.

The Transpose option consist of "turning" a range of cells on its side. Here is an example.

The Paste link option allows you to copy data located farther from the same worksheet, from another worksheet or from another workbook!

Excel will write the cell reference to view its content. In this case, the formula will be =$E$10.

This is a simple example. You can also make cell references coming from other worksheets in the same workbook. For example, paste link could also refer to =Sheet3!B25 which is the content cell B25 from the Sheet3 worksheet. The "!" is used to separate the name of the worksheet from the cell address.

The last paste link option is to copy the content of a cell from another workbook and to make a cell reference to it.

Open a new workbook.

Select the File tab.
Select New.
Select the Blank workbook option.
Press the Create button.
Place the cursor in the B5 cell.
Enter the value 500.

Select the File tab.
Select Save.
Save the workbook on your desktop under the name Link Example.

Copy the content of the B5 cell.
Return to the first worksheet.
Place the cursor in the A11 cell.

Press the arrow under the Paste button to view the options.
Select the option Paste link.

The reference in the cell will be ='[Link Example.xlsx]Sheet1'!$B$5. The name of the workbook is between brackets followed by name of the worksheet and the cell address. The link between the workbooks will remain active as long as the second workbook doesn't change location or change its name.

The next time that you will try to open this workbook, Excel will notice that there is a link to another Excel workbook.

Excel's security mesures will you ask you if you wish to active the link and retrieve the current value in the other workbook. This mesure is to make sure that you are not affected by viruses composed of macro commands.

The link toward another workbook may have been changed if the document was moved or if its name changed. It will continue with the last known value available or check the connections to reestablish the link.

In this window, you will notice that the source of the link was not found. You can sever a link or change the source. This last option allows you to search thru your computer or your network to find the Excel workbook that you need.

Here is the list of all the options available grâthis au collage special. You can decide of coller only a formula or sa value, a comment that you avez déjà écrit, one validation (list of choice) and also of formats of présentation. Another element intéressant is of coller one value by additionnant, soustrwith, multipliant or to diviser the value that is déjà in the cell. Here is a petit example:

Enter the value 250 in the cell A1.
Copy the content of the cell A1.
Place the pointer over the cell A1.
Select the option Paste special.
From the list of opérations, select the option Addition.
Press the OK button.

Notice that the content of the cell is now 500, or the values 250 and 250 added together. You can also use other mathematical operations (addition, soustraction,multiplication, division) to create the results you want.

Format painter

Une autre option very intéressante that n'est pas assez souvent used is of reproduce a format of présentation. It is very useful to recopier of styles of présentation and of format conditionnels. You avez créé a title to one partie of your model; déterminé the police of caractère, sa size and ses options of présentation. Au lieu of reproduce these options, you can copy the format.

Write in the cell A1 cell the text: Title of the first section.
Write in the cell A3 cell the text: Title of the second section.
In the A1 cell, change the font size to 18 points and the text color to blue.
Place the cursor in the A1 cell.

From the Clipboard, select Format painter.

The presentation options of of this cell have been selected. You can now apply them to a cell or a range of cells.

Place the cursor in the A3 cell.

The presentation style of the first cell will immediately be applied to the second. To reproduce the format to many cells, you must select the cell, or the range of cells, with the format of your choice, doubleclick for the Format painter button and apply it to all the cells of your choice. To stop, press once more on the Format painter button.

Like many commands, not all options are available on the screen; only the most popular ones.

This button will show or mask the clipboard window. When the clipboard is shown, you can cut and copy up to 24 elements. You only need to place the pointer in the cell of your choice and then press for the element to paste of choice from the clipboard. You can also press for the Paste all button to copy all the elements the clipboard in the order that you included them.

It is also possible of move, close or change the size the clipboard by pressing the button with the arrow to the right the clipboard title.

The Fonts group of commands allows you to choose the font, the size, put in bold, italic or underline, the border type and the cell's background color and pattern. These are the most often used options. Other options are available under the dialog box launcher.

Here is some more explications on these options.

The first box allows you to choose the font for your text. The box you donne one idée of the aperçu of the police. The nouvelle option to aperçu instantanné to Office 2007 allows you to view immediately the result avant same of the choose.

Que cela soit to determine a title or the text, you can by tout time change the size the text to qu'il répond better to your besoins. Instant previewe to Office 2007 allows you to viewthe impact of your changes avant same of choose the option.

Another way to change the size the text is to use the boutons increase or decrease the font size. The changement is immediate.

This option allows you to determine the color of fond of the cell. From of cette window,vous avez accès to 66 colors différentes. Theoption Other colors allows you to choose among more of 16 millions of tons of colors possibles.

The standard tab offers a colors pallet among the most popular colors. The personalize tab gives you a precise control on the colors that you want. You can donner one value of 0 to 255 to each basic color: red, green and blue. The total is 16 777 216 possible colors.

This option determines the color the text that you avez écrit. You avez accès aux mêmes options that to the color of remplissage of cells décrit above.

Excel ne you limite pas to write the text to the horizontal. You can change the orientation the text to mettre the emphase for the text or a title.

Select the or the cell dont you want change the orientation the text.
Select the one of options of présentation under the button Orientation the text.

Other options to orientation are available under the button of dialog box launcher.

This determines the amount of space between the content the cell's border. You can add more or less space between the text and the left cell border.

When the content of the cell surpasses the lenght of the cell, the text continues in the cell to the right if that cell is empty. Otherwise you will only see a part of the text. However, you can write as many rows of text in the same cell. The Wrap text option changes automatically the height of the cell to see all the text that you entered. You can also use the combinaison of ALT and Enter keys to indicate that you want to entrer text on many rows.

You must view the options to master all the possible options. Those that have already used Excel will be on familiar grounds. Except for a few changes to the presentation, you will find the same six tabs that where in previous versions of Excel. Experiment with these options and bring out a better presentation for your model.

Microsoft Excel offers many presentation styles for text, values and even for dates and times.

By pressing the arrow at the end of the box, you have a short list of available formats for values, text, dates and times. The last option, Other formats, allows you to choose among a greater list and even to personalize to your own presentation style.

By selecting Other formats from the last menu, or by pressing the dialog box launcher box from Numbers group of commands, you will access all the possible presentation formats. The Personalize category allows you to add your own formats.

There is also the other tabs, identical from the previous versions of Excel, that allows you to change the cell's presentation in many ways.

There are many possibilities under the Accounting number format. Is it in dollars, in Euros, Yens or another denomination ?

For other possibilities, select the More accounting formats.

From the Accounting category, you can choose among many more denominations than the ones mentionned in the previous list.

Allows to add or to remove decimal values from the presentation style. Excel will round up the value according to the number of decimals that you want show.

The conditional formatting options is an element of Excel that has been greatly improved with Excel 2007. Not, only does it allows you to change the presentation format of a cell according to its value, but allows you to realise some analyses on a range of values. This was only possible with specialized software before the arrival of Excel 2007.

You can select a range of cells and apply color scales to them. The cell's backgrand color will the represent it's value. In the color scale, the value at the bottom of the scale represents the lowest values. The color on top represents the highest values. You can also select the Other rules option to change how a color scale is applied.

You can also use a set of icons to represent a value. It will appear beside the value inside the cell. As you can see, the is wide selection of icons to select from. You can also select Other rules to determine how the icon set will be aplied to the values.

Creating a model is only the first part. You also need to adjust it to add or remove data from it. The next options allow you to manage your worksheet and your workbook. You can insert and remove cells, rows and columns by pressing just a few buttons. You can also format it to help its presentation.

You can insert and remove cells, rows and columns by pressing just a few buttons. At any time, you can add a cell, a row, a column in the worksheet. You can even add another worksheet if you need it.

This options can remove cells, rows or columns from the worksheet. This may be required to better organize your model.

Under the format option, you can adjust the lenght and height of a row or column. You can also mask them temporarily to better view that are far apart in the worksheet or that you don't want other users to see.

You can also change the name on the worksheet's tab or ven its color. You can also protect the entire worksheet so that no user can change the values unless you unprotect some cell that you allow them to change.

Apart from the Sum function, you will have a list of the most recently used functions and access to all the other functions.

Microsoft Excel has always had sort and filter options for data lists. This options is also available under the Data tab. This is where you will find all the instructions to use these options.

At times you need to change part of the data in your model. For example, a product changes name, a company changes address. You can use the options under the command to find and remplace data, a cell references in your formulas, or even the content.