Point and Click Method in Excel

You can use the point-and-click method in Excel to create formulas by selecting cells with your mouse rather than typing cell references manually.

With this method, you can create formulas quickly and accurately, especially when building complex formulas that reference cells across multiple worksheets within the same workbook or even from other workbooks.

In this tutorial, I will show you various examples of using the point-and-click method in Excel to build formulas. 

Example #1: Build a Formula Referencing Cells in the Same Worksheet

Suppose you have the laptop sales data below in a worksheet.

You want to enter a formula in column E that calculates the total sales of each laptop model.

You can use the steps below to build the formula using the point-and-click method:

1. Select cell E2 and type the equals (=) sign.
2. Click cell C2, the first cell you want to include in the formula.

Excel forms a moving border around cell C2 and inserts a reference to the cell in cell E2.

3. Type an asterisk (*), the multiplication operator, and click cell D2 the second cell you want to include in the formula.

Excel forms a moving border around cell D2 and inserts a reference to the cell in cell E2.

Note: If needed, you can continue to add operators and click on cells to complete your formula.

4. Press Enter. 

The formula calculates the total sales result in cell E2 based on the clicked cells.

5. Select cell E2 and double-click the fill handle to copy the formula to the other cells in the column.

You have successfully entered a formula that calculates the total sales for each laptop model in column E of the dataset by pointing and clicking cells in the same worksheet.

Relative, Mixed, and Absolute Cell References

You can use relative, mixed, or absolute cell references when creating formulas in Excel. 

In absolute and mixed cell references, Excel uses the dollar sign ($) to lock the reference to a specific row, column, or both ensuring that the referenced cells do not change or change only in certain ways when the formula is copied to other cells. 

The table below describes each type of cell reference.

Type of Cell Reference	Description
Relative	Example, A1. Has no dollar sign on the row and column reference. The row and column references adjust when the formula is copied to other cells.Default in Excel when you use point-and-click to build formulas referencing cells in the same worksheet or across multiple worksheets in the same workbook.
Mixed	Example, $A1 or A$1. Has the dollar sign on either the column or row reference.  If the dollar sign is on the column reference, the column stays the same but the row adjusts when the formula is copied to other cells.If the dollar sign is on the row reference, the row stays the same but the column adjusts when the formula is copied to other cells.
Absolute	Example, $A$1. Has the dollar sign on the column and row reference. The column and row are fixed and do not change when the formula is copied to other cells.

If you want to edit the formulas you create in Excel using point-and-click to make them absolute or mixed, you can press the F4 key to cycle through the different types of cell references.

Click inside the cell reference you want to modify and press F4 to cycle through the different types of cell references until you get the one you want.

Each press of F4 will change the reference as shown in the table below.

If the Cell Reference is…	One Press of F4 Changes it to…
A1	$A$1 (Absolute reference)
$A$1	A$1 (Mixed, absolute row)
A$1	$A1 (Mixed, absolute column)
$A1	$A$1(Back to absolute reference)

Also read: How to Reference a Cell on Another Sheet in Excel

Example #2: Build a Formula Referencing Cells Across Multiple Worksheets in the Same Workbook

The point-and-click method gives you a significant advantage over the manual entry when building formulas referencing cells across multiple worksheets in the same workbook.

With point-and-click, Excel automatically inserts cell references in the correct format of =SheetName!CellAddress. 

If the worksheet name in the cell reference includes one or more spaces, Excel encloses it in single quotation marks. For example, =‘Qtr1 Sales’!E2.

By automatically inserting cell references in the correct format, Excel prevents the typing and syntax errors that often occur when manually referencing cells across multiple worksheets. 

Suppose you have laptop sales data in the ‘Qtr1 Sales,’  ‘Qtr2 Sales,’  ‘Qrt3 Sales,’ and ‘Qtr4 Sales’ worksheets in a workbook and want to consolidate the data in the ‘Annual Sales’ worksheet in the same workbook.

Below is the dataset on the ‘Annual Sales’ worksheet where you want to consolidate the data.

You can use the steps below to build a formula to consolidate the data using the point-and-click method:

1. Click the ‘Annual Sales’ worksheet tab.
2. Select cell C2 in the ‘Annual Sales’ worksheet and type =SUM( as shown below.

3. Click the ‘Qtr1 Sales’ worksheet tab and click cell E2.

Notice  Excel has entered the cell reference (‘Qtr1 Sales’!E2) in the destination cell in the correct format ‘=SheetName!CellAddress.’ The sheet name is enclosed in single quotes because it has a space.

4. Type a comma after the first cell reference, click the ‘Qtr2 Sales’ worksheet tab, and click cell E2.

5. Type a comma after the second cell reference, click the ‘Qtr3 Sales’ worksheet tab, and click cell E2.

6. Type a comma after the third cell reference, click the ‘Qtr4 Sales’ worksheet tab, click cell E2, and type a closing parenthesis after the final cell reference.

7. Press Enter to enter the formula in the destination cell E2 of the ‘Annual Sales’ worksheet.

The formula calculates the total annual sales result in cell E2 based on the clicked cells.

Select cell E2 and double-click the fill handle to copy the formula to the other cells in the column.

You have successfully entered a formula that calculates the total annual sales for each laptop model in column E of the dataset by pointing and clicking cells in multiple worksheets of the same workbook.

Also read: Toggle Between Cell References (Absolute/Relative/Mixed) Shortcut

Example #3: Build a Formula Referencing Cells Across Multiple Worksheets in Different Workbooks

The point-and-click method gives you a significant advantage over the manual entry when building formulas referencing cells across multiple worksheets in different workbooks.

With point-and-click, Excel automatically inserts cell references in the correct format of =[WorkbookName]SheetName!CellAddress.

If the workbook name in the reference includes one or more spaces, Excel encloses it (including the worksheet name and square brackets enclosing the workbook name) in single quotation marks. For example, =‘[Annual Sales Report.xlsx]Qtr1 Sales!’E2.

By automatically inserting cell references in the correct format, Excel prevents the typing and syntax errors that often occur when manually referencing cells across multiple worksheets in different workbooks. 

Suppose you have annual laptop sales data in the ‘Year1.xlsx’ and ‘Year2.xlsx’ workbooks and want to consolidate the data in the ‘Year1&2.xlsx’ workbook.

Here’s how to do it:

1. Ensure the source workbooks with data (Year1.xlsx and Year2.xlsx) and the destination workbook where you want the formula ((Year1&2.xlsx) are open.
2. In the ‘Year1&2’ destination workbook, select cell C2 and type =SUM( to start the formula.

3. On the Windows taskbar, hover over the Excel icon and click the ‘Year1.xlsx’ workbook’s thumbnail to activate it.
4. Click the tab of the worksheet containing the annual sales dataset, and select cell C2.

Notice Excel has entered the cell reference (‘[Year1.xlsx]Sheet1!$C$2) in the destination cell in the correct format ‘=[WorkbookName]SheetName!CellAddress.’  Notice also that the cell reference is absolute. 

5. Type a comma after the first cell reference, hover over the Excel icon on the Windows taskbar, and click the ‘Year2.xlsx’ workbook thumbnail to activate it.
6. Click the tab of the worksheet with the annual sales dataset, select cell C2, and type a closing parenthesis. 

Note that Excel inserts absolute cell references when you create a formula referencing cells in different workbooks. 

Therefore, if you plan to copy the formula to other cells, make sure that you change the absolute cell references to relative before you copy as explained in the section, ‘Relative, Mixed, and Absolute Cell References,’ in Example #1.

7. Press Enter to enter the formula in the destination cell.

The formula calculates the Year 1 and Year 2 total annual sales in cell E2 of the destination worksheet based on the clicked cells.

8. Select cell C2 and remove the dollar signs in the absolute cell references in the formula to make them relative so you can copy the formula to other cells in the column.

Note: To make an absolute cell reference relative, click inside it (the cell reference after the exclamation point) and press F4 three times. 

9. Double-click the fill handle in cell C2 to copy the formula to other cells in the column.

You have successfully entered a formula calculating the Year 1 and Year 2 total annual sales for each laptop model in column C of the target worksheet in the destination workbook.

I have shown you how to use the point-and-click method to build formulas in Excel. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• Circular References in Excel – How to Find and Fix it!
• What Does the Exclamation Point Mean in Excel?