Set Excel text boxes to reflect data updates

by Mary Ann Richardson | Jun 26, 2007 12:00:00 AM

Tags: Mary Ann Richardson, Microsoft Office, Microsoft Excel, spreadsheets, desktops

Takeaway: Have your Excel text box change with your data! Here's how to create dynamic text boxes in Excel, while giving yourself the freedom to format them to fit your style.

Excel's text boxes do not have to be static; that is, you can have the text within the boxes change automatically to reflect changes in your data. For example, suppose you would like to create a worksheet that summarizes Year-to-Date Sales data from your Monthly Sales worksheet in a text box on Sheet1 of your workbook. The Monthly Sales worksheet lists the Months in Column A and the Total Monthly Sales in Column B. To create the text box for Year-to-Date Sales, follow these steps:

Open the Monthly Sales worksheet.
In cell F1, enter Total Year-to-Date Sales.
In cell G1, enter =SUM (B:B).
In cell H1, enter =CONCATENATE(F1, "=","$",G1)
Insert a blank worksheet and rename it Sales Summary.
Right-click the standard toolbar and then select Drawing.
Click and drag the text box tool to where you want your text box to be on the Sales Summary worksheet.
With the text box selected, click in the formula bar and enter =MonthlySales!H1.

You can right-click the text box and format it any way you wish. After you enter the sales total at the end of each month, the data in the Sales Summary text box will change to reflect the new Total Year-to-Date Sales.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

21 comment(s)