How to Create Floating Cells in Excel (2 Easy Methods)

In this article, I will show how to create floating cells in Excel. In many situations, we need to display a group of cells always floating on the screen while scrolling throughout the worksheet in Excel. If you want to know how to create floating cells in Excel, read this article carefully.

How to Create Floating Cells in Excel: 2 Useful Methods 

In this section, we will demonstrate 2 effective methods to create floating cells in Excel. For illustration purposes, I have taken a dataset where there are some salespersons and their corresponding amount of Sales. In the end, the total amount of sales is also given at the bottom.

Now, we want the Total Amount of Sales (C17) to be floated all the time on the screen. We can accomplish that by using the following two methods.

1. Utilization of Watch Window to Create Floating Cells in Excel

In the first method, we will use the Watch window feature to float a number of selected cells. The Watch window is a very useful feature to display not only the value of a cell but also the formula inside that cell. To use this feature, follow the steps below.

Steps:

• First, go to the Formulas tab.

• Now, click on the Watch Window option (positioned on the right corner).

• Consequently, the Watch Window dialogue box will appear like this.

• Now, we need to add Watch Window. To do that, click on Add Watch.

• Consequently, a new dialogue box will appear named Add Watch. In this new dialogue box, we need to select the cells that we want to display in the watch window. Here, I have selected B17:C17 from the sheet named  Watch Window. Finally, click on Add.

• As a result, you will see that all the selected cells’ values along with Formulas are being displayed in the watch window.

• Now if we scroll down the Excel sheet, the watch window will always be displayed on the screen.

• A beautiful thing about this watch window is that even if the cell value is changed it will be automatically updated in the watch window.
• Moreover, we can dock the Watch Window on top of the ribbon and it will stay there even if we go to another sheet.

In this way, we can create floating cells in Excel with the help of Watch Window.

Related Article: How to Remove Floating Cells in Excel

2. Application of VBA Code for Creating Floating Cells in Excel

In this example, we will create floating cells using a special VBA code. To learn more, follow the steps below.

Steps:

• Here, we will first take a picture of the cells that we want to float on the screen. To do that, first, select those cells. I am selecting cell B17:C17.
• Now, go to the Home tab, and click on Copy as Picture.

• Consequently, a new dialogue box will appear named Copy Picture. From there, click OK.

• And now, we need to paste the picture into the worksheet. Press Ctrl+ V to paste the picture. Now, click on the pasted picture, then go to the formula bar and write the =. Now, select the cells that you have copied as a picture. Here, I have selected B17:C17. Now, press Enter.

• As a result, this pasted picture has become dynamic. That means, if we change anything on the copied cells, it will be reflected in the picture as well.

• Now, we need to write a code that will ensure that this image will always be on the visible screen. To do that, open the VBA Editor by clicking Alt+F11.

• From the window, click on the worksheet name that you are working. Then paste the following code on the sheet window.

'Right Corner
Private Sub Worksheet_SelectionChange(ByVal A_Target As Excel.Range)
    Dim My_Picture As Object
    Dim My_Top As Double
    Dim My_Right As Double
    Dim Top_Right_Cell As Range
    '-----------------------------------------------------------
    '- position of the bottom right cell
    With ActiveWindow.VisibleRange
        AA_r = 1
        AA_c = .Columns.Count
        Set Top_Right_Cell = .Cells(AA_r, AA_c)
    End With
    '------------------------------------------------------------
    '- Setting position of the picture
    Set My_Picture = ActiveSheet.Pictures(1)
    My_Top = Top_Right_Cell.Top + 5
    My_Right = Top_Right_Cell.Left - My_Picture.Width
    With My_Picture
        .Top = My_Top
        .Left = My_Right
    End With
End Sub

• Now, save the code by pressing Ctrl+S. Then go to the main worksheet. Now, even if you scroll down, you will see that the picture will remain on the top.

In this way, we can make those selected cells float in Excel.

How to Create Floating Text Box in Excel

In this section, I will show you how to create a floating text box in Excel. For this section, I have taken a separate data set(In the picture below). Here, I have taken a worksheet where there is some information about 50 students of a College. The information includes Name, Student ID, Gender, and Blood Group. On the right side, we have a total no of O+.

Here, we will show a Text box that will contain the number of students who have O +ve as a blood group. In other words, we will create a text box that will contain the data of cell C17 and make the box float as we scroll. To do that, follow the steps below.

Steps:

• First of all, open the VBA editor by clicking Alt+F11. Then go to Insert >> Userform.

• As a result, a Userform will be created like this.

• Now, insert a module by going to Insert >> Module.

• Now, go back to the created UserForm1 by double-clicking on the menu bar located on the left side.

• Here in the bottom corner, you should see the properties of UserForm 1. If you don’t see the properties tab, you can right-click on the user form window and then select properties. Now, edit the caption and give it a suitable one. I am giving it Total Number of O+.

• Now, as the userform is empty, we can insert a text box or a label. I am taking a label named Label1. To do that, click on the A icon from the Controls in the Toolbox and draw a box on the user form window.

• Now, right-click on the user from the window and select view code. Consequently, the Code window will open up.

• Now, choose Initialize from the dropdown menu. (see the screenshot below)

• Now, write down the following code and clear the above portion.

Private Sub UserForm_Initialize()
Me.Label1.Caption = Format(Worksheets("Floating_Box").Range("G5").Value, "#")
End Sub

• Now, click on the worksheet to open the code of that sheet. Then from the left and right dropdown menu, choose Worksheet and Calculate respectively.

• Now, copy-paste the following code.

Private Sub Worksheet_Calculate()
UserForm1.Label1.Caption = Format(Worksheets("Floating_Box").Range("G5").Value, "#")
End Sub

• Now, we will write a code in Module1 to open a Macro. To do that, go to Module by clicking on Module1 and write the following code.

Sub ShowForm()
UserForm1.Show False
End Sub

• Now, go to the worksheet and then press Alt+F8 to open the macro list. Then select the ShowForm macro and run it.

• As a result, you will see that the form will appear on the worksheet containing the total number of O+. And even if we scroll through the worksheet, the form will still float.

• Now if we change O+ to something else, it will immediately reflect on the floating form.

That’s it. This is how we can create a floating text box in Excel very easily.

Things to Remember

In the 2nd method, after executing the VBA code, the undo command (Ctrl+Z) will not work. Also, to execute the code, you need to select a cell first to make the cells float.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

That is the end of this article regarding how to create floating cells in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.