Catégorie : Practical Excel

A worksheet in Excel is made up of cells. These cells can be referenced by specifying the row number and column letter. For example, A1 refers to the first row (indicated by 1) and the first column (indicated by A). Likewise, B3 refers to the third row and the second column.

The power of Excel lies in the fact that you can use these cell references in other cells when creating formulas. There are three types of cell references that you can use in Excel:

• Relative cell references
• Absolute cell references
• Mixed cell references

Understanding these different types of cell references will help you work more efficiently with formulas and save time (especially when copying and pasting formulas).

Relative Cell References

Let’s take a simple example to explain the concept of relative cell references in Excel. Suppose we have the following data set:

To calculate the total for each item, we need to multiply the price of each item by its quantity. For the first item, the formula in cell D2 would be =B2*C2 (as shown below):

Now, instead of entering the formula for each cell individually, you can simply copy the formula from cell D2 and paste it into all the other cells (D3:D8) using the fill handle. When you do this, you’ll notice that the cell references adjust automatically to refer to the corresponding rows. For example, the formula in D3 becomes =B3*C3, and in D4, it becomes =B4*C4.

These cell references that adjust when the formula is copied are called relative references in Excel. Relative cell references are useful when you want to create a formula for a range of cells and have the reference adjust automatically for each row or column. In such cases, you can create the formula once and copy-paste it across the range.

Absolute Cell References

Unlike relative references, absolute cell references do not change when you copy the formula to other cells. For example, suppose you have the dataset below and need to calculate the commission for the total sales of each item. The commission rate is 20% and is listed in cell G1.

To calculate the commission amount for each item’s sales, use the following formula in cell E2 and copy it to the other cells:

=D2*$G$1

Notice that there are two dollar signs $ in the commission cell reference $G$1. What does the dollar sign do?

A dollar sign, when added before the column letter and row number, makes the reference absolute (i.e., it prevents the row and column from changing when the formula is copied). For example, in the case above, when the formula is copied from E2 to E3, it changes from =D2*$G$1 to =D3*$G$1. Notice that while D2 changes to D3, $G$1 remains the same. This is because both the column G and the row 1 are locked with the $ symbol.

Absolute cell references are useful when you don’t want a reference to change when copying formulas. This is often the case when using a fixed value in a formula (such as a tax rate, commission rate, number of months, etc.).

While you could hardcode this value into the formula (e.g., use 20% instead of $G$1), using a cell reference allows you to update the value later. For example, if the commission rate changes from 20% to 25%, you can simply update the value in cell G1, and all formulas will update automatically.

Mixed Cell References

Mixed cell references are a bit more complex than relative and absolute references. There are two types of mixed references:

• The row is locked while the column changes when the formula is copied.
• The column is locked while the row changes when the formula is copied.

Let’s see how this works with an example.

Below is a dataset where you need to calculate three levels of commission based on the percentage values in cells E2, F2, and G2.

You can now use the power of mixed references to calculate all these commissions with a single formula. Enter the following formula in cell E4 and copy it across all relevant cells:

=$B4*$C4*E$2

The formula above uses two types of mixed references (one with the row locked, one with the column locked). Let’s analyze each reference:

• $B4 (and $C4) – The dollar sign is placed before the column letter but not before the row number. This means the column is fixed, but the row will adjust when copying the formula down. For example, when copying from E4 to F4, the column stays B and C, but the row changes if you copy downwards.
• E$2 – The dollar sign is placed before the row number but not the column. This means the row is fixed, but the column will adjust when copying the formula horizontally. For example, copying from E4 to F4 changes E$2 to F$2.

How to Change a Reference from Relative to Absolute (or Mixed)

To change a reference from relative to absolute, add a dollar sign before both the column letter and the row number. For example, A1 (relative) becomes $A$1 (absolute).

If you only have a few references to change, you can manually edit them in the formula bar or by selecting the cell and pressing F2.

However, a faster way is to use the F4 keyboard shortcut. When a cell reference is selected (either in the formula bar or in edit mode), pressing F4 toggles the reference type:

• Press F4 once → A1 becomes $A$1 (absolute)
• Press F4 twice → A1 becomes A$1 (row locked)
• Press F4 three times → A1 becomes $A1 (column locked)
• Press F4 four times → reference returns to A1 (relative)

Alternative text allows screen readers to capture the description of an object and read it aloud, providing support to people with visual impairments.

To add alternative text to an object in Microsoft Excel:

• Open your worksheet and insert an object (via Insert > Picture).
• Select the object.
• Right-click on the object, then choose Edit Alt Text from the context menu.

Unlike the Alt Text pane in Word or PowerPoint, Excel does not include the “Generate a description for me” option. You’ll need to write the description manually.

As a general rule, keep the alt text brief and descriptive.
You can omit unnecessary phrases such as « image of » or « photo of », as screen readers already indicate that the object is an image.

Charts and objects inserted into your workbooks have properties that you can update to control their behavior.
You can also add alternative text so that your worksheet is accessible to people who have difficulty reading text and graphics on screen.

Once an object is inserted, you can adjust its properties to control how it moves and resizes, whether it prints, and whether it is locked.

Steps to modify an object’s properties:

• Right-click on the image or object.
• Select Size and Properties. The Format Picture pane will appear on the right.

• Expand the Properties section.
• Choose a property option:

• Move and size with cells:
  If you resize the surrounding cells, the object will move with them. If you resize the cells that the object overlaps, it will also stretch accordingly.
• Move but don’t size with cells:
  The object moves with the surrounding cells if they are resized, but it will not stretch. It keeps its original size unless manually resized from the Format tab.
• Don’t move or size with cells:
  The object retains the same size and position even if the surrounding cells are resized.

Although Microsoft Excel is primarily used as a calculation program, in some situations, you may want to store images with your data and associate an image with a specific piece of information.
For example:

• A sales manager creating a product spreadsheet might want to include a column with product images,
• A real estate agent might want to add pictures of various buildings,
• A florist will likely want photos of flowers in their Excel database.

How to Insert an Image in Excel

All versions of Microsoft Excel allow you to insert images stored anywhere on your computer or a connected network. You can also add images from web pages or online storage such as OneDrive, Facebook, or Flickr.

Insert an Image from Your Computer

Inserting an image from your computer into an Excel worksheet is simple. Just follow these 3 quick steps:

• In your Excel worksheet, click where you want to place the image.
• Go to the Insert tab, in the Illustrations group, click Pictures, then choose This Device…

• In the Insert Picture dialog box that opens, locate and select the image you want, then click Insert.

The image will appear near the selected cell, specifically with its top-left corner aligned with the cell’s top-left corner.

To insert multiple images at once, hold down the Ctrl key while selecting the images, then click Insert.

You can now reposition or resize your image, or even lock it to a specific cell so that it resizes, moves, hides, or filters along with the associated cell.

Insert an Image from the Web, OneDrive, or Facebook

You can also add images from web sources using Bing Image Search. Here’s how:

• On the Insert tab, click Online Pictures.

• A window will appear. Type what you’re looking for in the search bar and press Enter.

• From the search results, click to select your preferred image, then click Insert.

You can also select multiple images to insert all at once.

If you’re looking for something specific, use the filters at the top of the search results to sort by size, type, color, or license.

In addition to Bing search, you can insert images stored on OneDrive, Facebook, or Flickr.

Paste an Image from Another Program

The simplest way to insert an image from another application into Excel is:

• Copy an image from another app (e.g., Paint, Word, or PowerPoint) using Ctrl + C.
• Return to Excel, select the desired cell, and press Ctrl + V to paste the image.

How to Insert an Image into a Cell

Typically, an image in Excel “floats” on a separate layer.
To insert an image into a cell (so it behaves like part of the cell):

• Resize the inserted image to fit into a cell. Resize or merge cells if necessary.
• Right-click the image and select Format Picture…

• In the Format Picture pane, go to the Size & Properties tab and select Move and size with cells.

Repeat for each image. You can even place multiple images in a single cell if needed.

As a result, your Excel sheet will be neatly organized with images linked to specific data.
When you move, copy, filter, or hide the cells, the images will follow accordingly.

Embed an Image in a Header or Footer

To insert an image in a worksheet’s header or footer:

• Go to the Insert tab, in the Text group, click Header & Footer.
• Click in the Left, Center, or Right header section. For footers, click “Add Footer” and select a section.
• Under the Header & Footer tab, in the Header & Footer Elements group, click Picture.

• In the dialog box, browse and insert your desired image. A placeholder will appear; click outside the header to display the image.

Insert Data from Another Sheet as an Image

Excel allows you to copy content from one sheet and insert it as an image into another sheet.
This is useful for summary reports or printing data from multiple sheets.

You can do this using:

• Copy as Picture: inserts a static image.
• Camera Tool: inserts a dynamic image that updates with changes.

Copy as Picture

• Select the data, charts, or objects.
• On the Home tab, in the Clipboard group, click the dropdown next to Copy, then click Copy as Picture…

• Choose As shown on screen or As shown when printed, then click OK.

• Go to the destination sheet, click where you want the image, and press Ctrl + V.

Use the Camera Tool (Dynamic Image)

To activate the Camera tool:

• Go to File > Options > Quick Access Toolbar.
• Under Choose commands from, select Commands Not in the Ribbon.
• Find and select Camera, then click Add > OK.
• The Camera icon now appears on the toolbar.

To use it:

• Select the cell range to capture.
• Click the Camera icon.
• Click the destination location in another sheet.

This creates a “live” image that updates automatically with changes in the source cells.

How to Modify an Image

Once inserted, you may want to reposition, resize, or style the image. Below are some common operations.

Copy or Move an Image

To move an image:

• Select it, then drag it with your mouse (the pointer turns into a four-sided arrow).
• Hold Ctrl and use arrow keys to move it pixel-by-pixel.

• To move to another sheet or workbook, press Ctrl + X, then Ctrl + V in the new location.

To copy an image:

• Select it and press Ctrl + C, then Ctrl + V to paste.

Resize an Image

The easiest way to resize an image is by dragging the corner handles.
To preserve the aspect ratio, drag from a corner.

Alternatively, use the Picture Tools > Format tab to enter exact width and height (in inches).
Entering one value will automatically adjust the other proportionally.

Change Image Colors and Styles

Excel offers many built-in effects for images. To access them, select the image and go to the Format tab under Picture Tools.

Useful options:

• Remove Background
• Corrections (brightness, contrast, sharpness)
• Color (tone, saturation, recoloring)
• Artistic Effects (paint/sketch-like)
• Picture Styles (borders, 3D effects, shadows)
• Picture Border
• Compress Pictures (to reduce file size)
• Crop
• Rotate (flip vertically/horizontally)

To restore original format and size, click Reset Picture.

Replace an Image

To replace an image:

• Right-click the image, select Change Picture, choose the new source (from file or online), select the image, and click Insert.

The new image will retain the same formatting and position as the previous one.

Delete an Image

To delete a single image:

• Select it and press Delete.

To delete multiple images:

• Hold Ctrl while selecting multiple images, then press Delete.

To delete all images:

• Press F5 to open the Go To dialog.
• Click Special…, select Objects, and click OK.
• All objects (images, shapes, WordArt, etc.) will be selected. Press Delete to remove them.

Be careful: this selects all objects on the sheet—ensure no important items are included before deleting.

Inserting Text Boxes

Unlike cell text, a text box can be placed anywhere in Microsoft Excel as a label or a comment on the worksheet in a separate window.
To insert a text box:

• Go to the Insert tab, then find the Text Box button in the Text section.

• You can now draw a text box—your cursor will immediately change to an inverted cross.

• Click anywhere on the worksheet to create the text box and type inside it.

You can change the background color of the text box, as well as the font, size, and color of the text it contains.

To move it, press and hold the mouse to drag the dotted border to your desired location. You can also rotate it using the rotation handle above the box.

Insert a Text Box Using the Shapes Menu

You can also insert a text box through the Shapes menu:

• In the ribbon, go to Insert > Illustrations > Shapes > Text Box.

• The Text Box icon will also appear in the Recently Used Shapes group if you’ve inserted one recently.

How to Remove the Text Box Border

• Click the text box. A new tab will appear in the ribbon: Shape Format.

• Go to Shape Format > Shape Outline > No Outline to remove the default border.

You can also use Shape Fill to change the fill color and Text Fill to change the text color.

Using Shapes

Excel gives access to various customizable graphic objects called Shapes. You may want to insert shapes to create simple diagrams, display text, or add visual interest.

Keep in mind: Shapes can add unnecessary clutter. Use them sparingly—they should enhance, not dominate.

Inserting a Shape

To add a shape:

• Go to Insert > Illustrations > Shapes to open the Shapes gallery.

• Shapes are grouped by category, with Recently Used Shapes at the top.

To insert a shape:

• Click a shape, then click anywhere on the sheet to place it with default size.
• Or, click and drag to define custom size and proportions.

Once placed, the shape is selected and named.

You can also insert a shape inside a chart. Just select the chart first, then insert the shape—it becomes embedded and moves/resizes with the chart.

Some shapes require special drawing:

• Freeform: Click repeatedly to create lines, or click-and-drag for irregular shapes. Double-click to finish.
• Curve: Click multiple times.
• Scribble: Drag the mouse pointer freely; closing the shape will make it filled.

Tips:

• Shapes have names (e.g., “Rectangle 1”). You can rename them in the Name Box.
• Click a shape to select it.
• Hold Shift while dragging to maintain proportions.
• Hide/display all objects via File > Options > Advanced > Display options for this workbook.

Modifying Shapes

To modify a shape:

• Click to select it.
• Right-click and choose Format Shape.

• The Format Shape pane appears on the right. You can adjust fill type and color, border color and thickness, add shadows, glow, reflections, and more.

Using Shapes as Macro Buttons

You can use a shape (e.g., a rounded rectangle) as a macro button:

• Go to Insert > Illustrations > Shapes, then pick a shape.

• Click the sheet to insert the shape.
• The shape appears with a default name.

To make it look like a button:

• Select the shape, go to Shape Format, then Shape Styles > More.

• Choose a style like Intense Effect (for a 3D shadow).

• For consistent styling, go to Page Layout > Themes > Effects > Office 2007-2010.

To add a label:

• Right-click the shape, type the button text, then click the border to exit text-edit mode.
• Format the text (bold, size, alignment) via the Home tab.

To assign a macro:

• Right-click the shape > Assign Macro…

• Choose the macro from the list > Click OK.

SmartArt Graphics

SmartArt lets you visualize information with graphics rather than plain text. It offers various styles for different concepts.

To insert a SmartArt graphic:

• Place the cursor where you want it to appear.
• Go to Insert > Illustrations > SmartArt.

• A dialog opens: select a category, pick a graphic, 

then click OK.

To add text:

• Select the SmartArt. The SmartArt pane opens on the left.
• Enter text next to each bullet. The text appears inside the shapes.
• Press Enter to add more bullets and shapes. To delete, remove bullets.

Alternatively, click a shape to type directly. For complex graphics, the pane is faster.

Editing SmartArt Layout

To add a shape:

• Select the SmartArt and go to the Design tab.

• Select a nearby shape.

• Click Add Shape, then choose:
  • Add Shape Before/After (same level)
  • Add Shape Above/Below (different level)

To rearrange:

• Select the SmartArt > Design tab.
• Select the shape.

• Click Move Up/Down to change its order—subordinate shapes move with it.

Customizing SmartArt

Use the Design and Format tabs to customize:

• Click Change Colors to use a color scheme from your document’s theme.

• Color sets vary by theme.

Changing SmartArt Layout

To modify the layout:

• Select the SmartArt > Design tab.
• In the Layouts group, click the dropdown for more layouts.

• Pick a new layout. If it’s too different, some text may not display

Verify before confirming.

WordArt

You can enhance a spreadsheet by formatting cells and fonts, or by using WordArt—a powerful feature in Excel. For instance, you might use it to style a range header instead of regular text.

Inserting WordArt

To insert WordArt:

• In the worksheet, go to the Insert tab.
• Click Text > WordArt.
• A dropdown appears with multiple styles—choose one.

• Type your text in the box.

• Click anywhere else on the sheet to complete.

Editing WordArt

After insertion:

• Move it to the right place (e.g., top of the sheet for a header).
• Clicking it activates Drawing Tools.
• Under Shape Styles, pick a default or custom style.
• Under WordArt Styles, format the text’s color, outline, and effects.

The result is often more visually appealing than regular font formatting—consider using WordArt next time to enhance your spreadsheet’s appearance.

When we create a chart using data on a worksheet, by default, the chart is created on the same sheet and can be moved using the mouse. However, sometimes it’s better to have a standalone chart that fits entirely on its own sheet and remains fixed in place. In this article, we’ll learn how to move a chart from a worksheet to a new chart sheet.

Steps to move the selected chart to a new chart sheet:

• Select the chart and go to the Design tab.

• You will see the Move Chart icon on the far-right corner of the ribbon in Excel. Click it.
• In the Move Chart dialog box, click the New sheet radio button. Change the chart name if desired.

• Click OK. The chart will be moved to a new chart sheet.

In a chart sheet, the chart is properly fitted and fixed in place, but you can still perform all chart-related tasks. This sheet belongs to the workbook. The chart is designed to fit this sheet, making it ideal for data presentation.

You can also move other charts onto the same sheet, but those charts will float while the first chart remains as the sheet background.

You can also move the chart by right-clicking on it. When you right-click the chart, you will see an option Move Chart…. Click it. The same Move Chart dialog box will appear.

Moving a chart from one worksheet to another
If you want to move the chart to another standard worksheet, select the Object in option. From the dropdown list, select the desired worksheet name. Press OK. The chart will be moved. The same can be done using standard cut and paste.

Moving a chart from a chart sheet back to a worksheet
On the chart sheet, right-click the chart. Click Move Chart. Select Object in and choose the desired sheet from the dropdown list.

Press OK. The chart will be returned to the standard worksheet. The chart sheet will be deleted.