Q. I receive a spreadsheet each week where all sales data is categorized by its product number, but the product number is only listed on the first row of sales data. It is then subcategorized by the product level, which is also not listed on each row of sales data. I have to manually add this information in order to complete the analyses required. Is there a better way to fill these blank cells?
A. I have created an example spreadsheet to mimic the issue described in this query. See the screenshot below for a snippet of the spreadsheet. You can access a copy of this workbook here.
I have seen many people handle a spreadsheet like this by copying cell A3 down to A15, then copying cell B3 down to B7, copying cell B8 down to B12, and so on. That’s time-consuming, especially if you have hundreds or thousands of rows. But it’s not necessary. There is a quick way to complete this task.
First, select the data by clicking somewhere within the data and click Ctrl+A. Then click Ctrl+G to open the Go To window and click Special. Within the Go To Special window, click Blanks and OK. In this example, the cursor will now be active in cell B4. Enter =B3 and click Ctrl+Enter.
Now, convert the formulas into values so that you can sort and analyze the data. Select the data by clicking somewhere within the data and clicking Ctrl+A. Click Ctrl+C to copy, then click Ctrl+Alt+V. Choose Values, then OK. Your spreadsheet is now populated with all categories and subcategories and ready for analysis. See the screenshot below for a snippet of the final version of the spreadsheet.
Note that the content is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
A video demonstration of this process is available at the bottom of this article.
About the authors
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
