How to Fill Blank Cells With the Value in the Cell Above

Published on February 22, 2017

Filling Blank Cells With the Value in the Cell Above

Today, let’s learn how you can fill blank cells in a table with the values in the cell right above it.

Click here to download the example file, which will help you understand the concept better.

If you see the below screenshot of the example file, you will see that the first column of the table has Parent Product codes, and the last column has the prices of the Child Products. And we need to sum up the prices of all the child products to find the price of the parent product.

challenge1solution1

This can be achieved in more than one way, but I will be discussing the method I find most easy to use. Below are the steps to achieve our goal.

  1. Select Column A in the table and press Ctrl + G to open the ‘Go To’ dialogue box. Then press Alt + S to open the ‘Go To Special’ dialogue box. Then press K key to select the ‘Blank’ option. Press OK to close the dialogue box. Now you will see that all the blank cells in the column have been selected.

challenge1solution2

2. Then make sure that Cell A3 is the active cell and type = and then select cell A2. Then press Ctrl + Enter. Now you will see that all the blank cells in Column A of the table have been filled with the code of the Parent Product. [This is what I want teach in this post]

challenge1solution3

3. Now all we need to do is to use the SUMIF function to find the total of the prices of all the child products to get the price of the parent product. So type the following in column J4

=SUMIF(A:G,I4,G:G)

and then pull it down till Cell J53 to find the prices of all parent products.

challenge1solution4

That is it!

Hope you found this post interesting.

If you want to get notified by email whenever I post such tutorials, make sure you subscribe to the Mad About Excel newsletter by filling out the below form.

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

Please share what you felt about this post, or tell me any alternate solution you have, by commenting below.

Thanks for reading. Have a great day. Happy Excelling! 

 

HUGE LIMITED TIME DISCOUNT ON THE BEST ADVANCED EXCEL COURSE ENROLL NOW!!
EXCEL PRINT TIPS
9 Excel Print Settings You Probably Don’t Know About
excel_look_professional
Making Excel Worksheets Look Professional – A Case Study
14 Excel tips
14 Excel Tips – Make Yourself Super-Efficient in Excel
DATA CLEANING EXCEL
Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques
FREEZE PANES
Freeze Panes in Excel – The Complete Guide
HOW TO LEARN EXCEL
How to Learn Excel and Be a Spreadsheet Champion
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
BECOME AN EXCEL NINJA COMPLETE REVIEW
Become An Excel Ninja – Course Review
BEST EXCEL COURSES
10 Best Online Courses to Make You an Excel Wizard
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
How to Sort Excel Worksheet in Alphabetical Order
How to Sort Excel Worksheets in Alphabetical Order
VBAPOST1
How to Add And Name A New Sheet At The Same Time Using Excel VBA
ROUNDING NUMBERS IN EXCEL
Rounding Numbers in Excel – The Complete Guide
number_of_words
How to Find the Number of Words in a Text String in Excel
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
How To Indentify ULP case in a text string
How To Identify If A Text String Is In Upper, Lower Or Proper Case
Pivot Tables
How to Insert a Pivot Table in Excel – Pivot Table Basics
How To Convert Pivot Table Into Frequency Distribution
How to Convert Pivot Table into a Frequency Distribution in Excel

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

Share
Tweet
+1
Pin
Share
Stumble
Enjoyed this video?
How to fill blank cell with value above
"No Thanks. Please Close This Box!"