Excel: Merge and center cells

Excel: Merge and center cells

YOUR OBJECTIVE

In your Excel table, you added a major heading that applies equally to several columns of data. You want this heading to be centered over those columns. To accomplish this, you want to merge several cells into one and center the title within it.

In this learning module “ Excel merge and center”, you will discover how to merge multiple cells into one and how to center the contents within it .

Excel merge and center:HOW DOES IT WORK?

To merge several cells into one and center the contents (a heading, for example) within it, first select the cells you want to merge:

Then – on the Home tab, in the Alignment group – click the excel Merge and Center button.

The selected cells will be merged into a single broad cell with the content centered within it:

When you click the small black arrow on the excel Merge and Center button, a list containing additional merge commands will appear:

Excel Merge and Center: All cells in the selected range will be merged horizontally and vertically. Text will be centered horizontally.

If data (text or numbers) were entered in more than one cell of the selected range, only the contents of the upper-leftmost cell will be retained. But before this happens, you will be given a warning.

With the excel Merge Across command, cells in the selected range are horizontally merged, row by row.

The excel Merge Cells option merges all cells in the selected range, horizontally and vertically, into a single cell.

Unmerge Cells: Cells in the selected range are unmerged.

Excel: Introduction to functions

introduction_1

YOUR OBJECTIVE

Because of the amount of typing involved and the typing mistakes you sometimes make, you feel it is troublesome to use formulas to add together any more than two numbers. For that reason, you would like to find a faster and less error-prone method to do so. Moreover, you often deal with lists of numbers for which you would like calculate the numerical average; sometimes you also want to know how many values appear in a list. For these and many other purposes, Excel is equipped with a variety of functions that can handle large amounts of data and accurately perform many services for you.

In this learning module “ Excel: Introduction to functions”, you will find out what Excel functions are and how to use them.

Excel Introduction – HOW DOES IT WORK?

Excel Introduction:  One of the most frequently used functions is SUM. This function is used to add up a series of numbers. Of course, you could always enter a formula that references each and every value in a list – for example: = D1+D2+D3+D4+D5+D6+D7+D8+D9+D10+D11. But that would involve a lot of typing and clicking and would demand your fullest concentration so as not to make a mistake. Beyond that, your results would turn out incorrect if you happened to miss referencing even one of the cells. With long lists, it would be impossible to type references to all the cells yourself.

The preceding illustration shows you the general construction of an Excel function. Functions always start with an equal sign (=) followed by the name of the function in capital letters. (You actually can write the name in lowercase letters if you want; if you spell it correctly, Excel will automatically convert it to uppercase.) The function name is followed by a pair of parentheses. The function’s arguments are always bracketed by those parentheses. Arguments are information that a function requires to calculate its result. The number of arguments permitted or required by a function varies from function to function. In a function that uses more than one argument, the arguments are separated by semicolons (;). Some examples:

=SUM(E5:E16) –> 1 argument

=TODAY() –> no argument

=IF(C13>35;C12+5;C12+8) –> 3 arguments

In general, functions are used in a fashion very similar to formulas:

  • Display mode can be used for identifying cell references.
  • Functions can be copied to other cells.
  • Absolute references, if needed, can be created by pressing the [F4] key.

Excel: Lock (freeze) rows and columns

Excel: Lock (freeze) rows and columns

YOUR OBJECTIVE

It is often difficult to maintain an overview when working with big tables in Excel: What information is in column D? What name is in row 19? In such instances it is useful to lock the first row or the first column, so that it is always visible, even when you scroll down or to the left in the table. In this learning module “((fett)) Excel: Lock ((fettende)) rows and columns”you will learn how to lock rows and columns.

Excel: Lock rows and columns – HOW DOES IT WORK?

Excel: Lock the top row

It is helpful to lock the first row containing the column heading when you are preparing a long list in Excel. This ensures that it is always visible, even when you scroll down in the table. To lock the top row, go to the View tab and click on Freeze Panes in the Window group.

Select Freeze Top Row.

If you now scroll down, you will see that the first row with the column heading remains visible.

You can lock the first column containing the row description in the same way.

Excel: Lock the first column

Click once more on Freeze Panes and select Freeze First Column.

If you now scroll to the right, you will see that the first column remains visible.

Excel: Unlock panes

If you want to unlock the panes again, select Unfreeze Panes by clicking on Freeze Panes in the Window group in the View tab.

Excel: Locking multiple rows and columns

You can also freeze and lock several rows and columns at the same time. Select the cell to the top and left of which all rows and columns should be locked.

Click again on Freeze Panes and select Freeze Panes.

 

If you now scroll down, all rows above the selected cell remain locked, as do all columns to the left of the selected cell if you scroll right.

Excel cell references: Working with cell references to other files

Excel cell references: Working with cell references to other files

YOUR OBJECTIVE

Your company, an ice-cream retailer, does business in several cities. In an Excel workbook, you recorded your company’s first quarter’s sales from all business locations, broken down by ice cream varieties. To obtain a clearer overview, you listed the sales for each month on a separate worksheet. That is, there is a worksheet for January, another for February and one for March, as well.

Now, in a separate file, you want display all three months (January – March) of sales data for just the Madrid location. Because you want this new table always to reflect changes, if any should be made to the original data, you must use excel cell references to dynamically link the data display in your new table to the source data .

This learning module will show you how to work with excel cell references that link the cells in an Excel table to cells in another file.

HOW DOES EXCEL CELL REFERENCES  WORK?

Creating a excel cell reference between cells of separate Excel files is very similar to linking cells between different worksheets. However, before you can link cells from one Excel file to cells in another Excel file, both files must already be open.

To enter a excel cell reference to a cell in another file, first click the cell in which you want to enter the excel cell reference. Then enter an equal sign (=). Now position your mouse pointer over the Excel program icon at the bottom edge of your screen (in the taskbar). Move your mouse pointer to the thumbnail preview of the file with the source data and click on it. That workbook now appears on your desktop. If necessary, select the appropriate worksheet in that Excel workbook. If you now click on the cell that contains the source data and press [Enter], Excel will again display the spreadsheet where you began creating the excel cell reference, which now is complete, showing the data from the ‘other’ file.

Here is a specific example:

To create the summary table for the Madrid location, first open both files, the file containing the original tables and the file in which you want to display just the Madrid data. Start with the target file. In our example, you can see that, in preparation for the Madrid data, the row and column headings have already been entered. To enter a cell reference that will link the first target cell in the new table to the one that contains the sales number for chocolate ice cream in January, click cell B4 of the new table and type in an equal sign (=). To continue creating the cell reference to the other file, use the taskbar to go the other workbook.

CAUTION

To create a link between cells of separate Excel files, all involved files must be open.

That workbook will now appear on your desktop. Make sure you are positioned on the January worksheet. There, click the cell to which the excel cell reference is to refer, in this case, cell C4.

Now press [Enter]. Excel cell reference completes and returns focus to the table in which you began entering the cell reference.

HINT

As the excel cell reference is being constructed, you can observe it in the formula bar. The complete cell reference appears here, which in our example should now read: . The part within the square brackets shows the reference to the ‘other’ file. The next part, up to the exclamation mark (January!) indicates the worksheet. After that, the specific cell is identified – here, C4. It is important to note that the cell coordinates are formed as an absolute reference: $C$4.

HINT

In our example, when you finish entering the cell reference by pressing the [Enter] key, Excel automatically returns the new Madrid workbook to the front of the desktop.

CAUTION

Now, in the example, you can use the AutoFill function to enter the remaining cell references for the other ice cream sorts in the January column. However, before you can do that, you must first change the absolute cell reference in B4 to a relative cell reference. (For more information, refer to the learning module: Relative and absolute cell references.) To do this conversion, click on the cell reference $C$4 in the formula bar. Then press the [F4] key as often as necessary, until both dollar signs have been removed from the cell reference. In the end, the cell reference should look like this:
=[Icecream_Sales_3_8_3_3.xlsx]Januar!C4
When you are satisfied with the excel cell reference, press [Enter]. Now you can use AutoFill to fill out the rest of the column. Position your mouse pointer over the lower right-hand corner of cell B4 until it turns into a black cross: . Then click and hold the left mouse button to drag the pointer down to cell B7. When you release the mouse button, you will see that the excel cell references for the covered cells have been filled in and that the values for the other ice cream varieties and the total value will be displayed.

HINT

For the table to retain its pre-set format, click the SmartTag that appears below the filled-in range of cells. In the brief selection list that appears, activate the Fill Without Formatting option .

You can now proceed in the same fashion to link to the February and March data in the remaining columns. Use your mouse to enter a cell reference in cell C4 of the Madrid worksheet to link to February’s ice cream sales data in the source worksheet.
=[Icecream_Sales3_8_3_3.xlsx]February!C4
Then use the AutoFill function to fill in the remaining cells in the February column. Don’t forget to convert the absolute excel cell reference ($C$4) to the relative excel cell reference (C4) first.

Finally, create the reference =[Icecream_Sales_3_8_3_3.xlsx]March!C4 in cell D4 for the month of March, and then fill in the remaining cells as described above.

Once you close the Icecream_Sales_3_8_3_3.xlsx file, the excel cell references in the target workbook (Madrid) will automatically be expanded to contain complete paths (e.g., ().

This guarantees that the spreadsheet in the Madrid file will always display the current data for the first three months of the year. As long as the saved path to the ‘other’ file remains valid – i.e., the file remains in the same name and location, and the relevant data cells stay in the same positions – the data displayed in the new worksheet will always reflect the most recent data in the ‘other’ file.

Excel edit a chart: Repositioning and resizing a chart

Excel edit a chart: Repositioning and resizing a chart

Excel edit a chart: YOUR OBJECTIVE

In an Excel table, you recorded your company’s sales for the first three quarters of 2010. You already created pie charts for the first and second quarters, each on its own separate worksheet. But now you would like to shift both of those charts to the worksheet with the table containing the underlying data.

In this learning module “Excel edit a chart”, you will be shown how to reposition and resize charts.

Excel edit a chart: HOW DOES IT WORK?

When creating a chart, you can generate it either on a separate worksheet or as an object on the same worksheet as the underlying data. In either case, you can move the chart to the other location.

Excel edit a chart: Assuming you generated a chart on a separate worksheet and now want to move it to the worksheet with the underlying data, go first to the worksheet that contains the chart (as shown below):

Then activate the chart by clicking on it. As soon as you do that, the Chart Tools context tab will appear in the Ribbon along with its subordinated Design, Layout and Formattabs:

Now click the Design tab to reveal its functions in the Ribbon:

On the Design tab, in the Locationgroup, click the button:

The Move Chart dialog box (explained below) will appear:

New sheet: Activate this option to place the chart on a separate worksheet. At the same time you activate it, you can type in a name for the new worksheet.

Object in: Activate this option to move the chart onto a worksheet of your choice. It is not uncommon to move a chart to the worksheet that contains the data table.

Since, in our current example, you want to move the chart to an existing worksheet, click the small black arrow button  of the Object in: option.

A list of all worksheets in the current workbook will appear:

For the current example, select the Revenues 2010 worksheet:

Conclude by clicking OK.

Excel edit a chart: As a result, Excel moves the chart onto the Revenues 2010 worksheet and deletes the Quarter1 worksheet:

If you move the chart so that it appears on the same worksheet as the underlying table, as in our example, you can expect Excel to to position it and reduce its size in such a way that overall readability of data and chart is maintained.

Excel edit a chart: If you want to move a chart to some other position on its worksheet, place your mouse pointer on the chart’s frame. The mouse pointer will change to a four-way arrow:  . Then, while holding the left mouse button depressed, you can pull or push it to a new location:

In our example, you want to place the pie chart for the second quarter’s sales onto this worksheet, as well. To make room for the Quarter 2 chart, you will want to move the Quarter 1 chart until it is flush with the left edge of the table, and then reduce it in size so there will be room for the next chart just to its right.

Excel edit a chart: To reduce the chart’s size, select the chart by single-clicking the frame surrounding it. When you move your mouse pointer to any of the frame’s corners, the pointer will change to a slanted double-headed arrow (shown below):

Now, while holding the left mouse button depressed, pull the corner in toward the middle of the chart. Release the button when you are satisfied with the chart’s size:

 

Excel edit a chart: HINT

If you want to maintain the ratio of the chart’s height to width, press and hold the [Shift] key while resizing it.

Excel delete rows:Deleting cells, rows, or columns

delte_rows_1

YOUR OBJECTIVE

An Excel worksheet has too many empty rows between the heading and the contents. You don’t like this and want to delete an empty row. There is also a column with sales in British Pounds in your table, which you would like to remove.

In this learning module “Excel delete  rows” you will learn how to delete rows or columns.

Excel delete rows: HOW DOES IT WORK?

Before you delete cells, rows or columns from your table, you have to select them first.

On the Home tab, in the Cells group, click the  button. A click on this button will delete the rows, columns, or cells selected.

You can also delete one or more cells in the same way. Select them and click . The selected cell or cell range will then be deleted. Cells will be shifted upwards into the gap created.

You can also delete a cell, row or column by selecting a cell within the row or column you want to delete, and then clicking the arrow on the button. As your next step, select whether you want to delete the selected cell, or the column or row where the cell is located.

 

When you select Delete Cells…, the following dialog box where you can determine what action you want to take will open:

 

If you want to delete an individual cell and fill the gap by shifting the cells on its right to the left, select this option.

If you want to delete an individual cell and fill the gap by shifting the cells below it upwards, select this option.

If you want to delete the entire row where the selected cell is located, select this option.

To delete the entire column where the selected cell is located, select this option.

Excel delete rows: KEY COMBINATION:

You can also delete cells, rows, or columns with the [Ctrl]+[-] key combination.

 

Excel cell color: Coloring and shading cell backgrounds

cell_color_1

YOUR OBJECTIVE

You already formatted your Excel table. Now you want to add color to your table by using background colors, i.e., shading.

In this learning module “excel cell color”, you will find out how to fill the backgrounds of cells and cell ranges with colors.

 

HOW DOES EXCEL CELL COLOR WORK?

First select the cell or cell range that you want to fill with background color. Then click the small black arrow on the Fill Color button (Home tab, Font group).

The Theme Colors catalog will appear. Here you can select the color of your choice by clicking on it. The background(s) of the selected cell(s) will now be filled with the selected color:

 

HINT

If you do not see a color you like, click .

If you want to control the fill-color characteristics more closely, click the dialog box launcher on the Home tab, in the Font group.

 

The Format Cells dialog box will appear. Now go to the Fill tab.

 

In the Background Color area, you will find a variety of colors. Click on the color you want to use.

If you are looking for a special color, you can find a greater variety or mix a color yourself by clicking More Colors…

In the Pattern Color and Pattern Style selection lists, you can specify a pattern style to serve as a background and color it as well.

To use a special color effect (i.e., graduated shading) on cell backgrounds, click Fill Effects…

If you do, the Fill effects dialog box will appear:

 

In the excel cell Colors area, you can specify how many colors you want to use for the fill effect.

Select the excel cell colors you want to use in the Color 1 and Color 2 selection lists.

Use the Shading styles area to select a look that suits your purpose.

For some shading styles, you can also apply a Variants option.

In the Sample area, you can see the result of your current settings.

When satisfied with your settings, click OK.

 

Excel: Slicers – Pivot table

Excel: Slicers – Pivot table


Excel: Slicers – YOUR OBJECTIVE
You created a pivot table and now want to filter it using a variety of criteria. Until now you’ve used report filters to do this. A disadvantage of report filters is that it is not always easy to recognize which items have been filtered, especially when multiple filters have been applied. Excel now offers Slicers that give you the capability of filtering pivot tables quickly and dynamically, but above all in such a fashion that you can immmediately recognize the current filtering state. [Read more...]

Excel Addition: Combining data – pasting with mathematical operations

Excel Addition: Combining data – pasting with mathematical operations

YOUR OBJECTIVE

To record the monthly revenues at your company’s several business locations, you created a number of spreadsheets in an Excel workbook (file). Excel addition  can help you to combine data fast and effective. To be able to see the data more clearly, you decided to record the revenues for each month on a separate worksheet. So far, you have a worksheet for January, another for February and a third for March.

Excel addition can be so helpful for companies e.g. For analysis purposes you also created a table on a separate worksheet that you will be using to accumulate sales results for the running year, adding new data to existing data each time the sales data for another month is available. So far, you’ve accumulated sales figures from January and February. Now that the March data is in, you would like to add the results from March to the existing numbers in your summary table. The Paste Special function will help you accomplish this in a quick, straightforward fashion.

In this learning module, you will be shown how to carry out mathematical operations while you paste.

HOW DOES EXCEL ADDITION WORK?

In Excel, after copying data from a cell or a range of cells, you can use Paste Special options to carry out certain mathematical operations while pasting data. Assume you have three worksheets, each containing sales figures for various flavors of ice cream for the months of January, February and March. Now you would like to sum up these figures on a fourth worksheet in order to display sales per flavor for the quarter.

To do this, you would first copy the table from the January worksheet to the newly created Cumulated worksheet. Then, to add the February sales to the sales figures for January, go first to the February worksheet. There, select the cell range with the sales numbers, and then use the Copy function to copy it to the clipboard. Now go back to the Cumulated worksheet. After positioning the mouse pointer on the first cell of the target cell range, click once. Now, on the Home tab, in the Clipboard group, click the little black arrow on the Paste button. In the selection catalog that appears (shown below), choose the Paste Special… command.

The Paste Special dialog box will open. Here you can specify which mathematical operation you want to carry out when pasting.

The following options are available in the [mathematical] Operstion area:

Use this option to specify that no mathematical operation should be carried out when pasting.

Activate this option, to cause the copied data to be added to the data that already exists in the paste area.

This option specifies that the copied data is to be subtracted from the data in the paste area.

Here you cause the copied data is to be multiplied by the data in the paste area.

This option causes the values in the paste area to be divided by the copied data values.

Activate this option to prevent values in the paste area from being changed if there is a blank cell in the corresponding cell position in the copied data.

Activate this option to cause columns of copied data to be changed to rows and vice versa.

When copying All or All except borders, this option causes links to the copied data to be inserted into the pasted cell area.

In the present example, you want to add the values from the clipboard to the values in the paste area. To do so, select the Add option, and then click OK.

You can then proceed in the same fashion to add the totals from March to the summary table, resulting in a summary of all sales for all three months. As this example shows you excel addition is a good and effective mathematical operation for combining data.

Excel font: Changing font, font style, font size

Excel font: Changing font, font style, font size

YOUR OBJECTIVE

To present your data in Excel more effectively, you can, among other things, change the character of the type fonts you use.

In this learning module “Excel font”, you will discover how to change fonts, font style, and font size in your Excel table.

Excel font: HOW DOES IT WORK?

You’ve already laid out your table and entered some data. Now you want to visually differentiate certain areas in the table from others. To change excel font and excel font size, you first have to select the relevant cells.

HINT

To apply a format to every cell at once, click the Select All button at the top-left corner of the table.

All formatting you undertake will now be applied to all cells in the active worksheet.

KEY COMBINATION:

You can also select all cells in a worksheet by using the [Ctrl]+[A] key combination.

On the Home tab, in the Font group, click the small black arrow next to the font name to make the Font selection list appear.

The excel font box names the font currently selected for active cell(s), e.g., Calibri. When you open the selection list, however, all installed fonts are displayed:

As you move your mouse over the fonts in the list, live preview alters the selected text in your table to show the effect each such font would have.

Now choose an excel font you like by clicking on its name. The selected text will then be formatted using that font.

HINT

Less is more! Only in exceptional cases is it advisable to use more than a single font in one worksheet.

You can change the font size in a similar fashion. First select the relevant cell(s).

Then – on the Home tab in the Font group – open the Font size selection list. If you choose a taller font size, row height will automatically be adjusted to suit it.

To change the style of font being used to bold, italic or underlined, simply click the corresponding buttons  in the excel Font group.