 |
|
| TheExcelAddict.com | |
| November 12, 2019 | |
 |
|
| Hi fellow Excel Addict, | |
| For
the past several months I have
been working on a remodeling project at a rental
property I own an hour's drive from my home. It's has taken much longer than expected, however, I'm expecting to be finished that project by the end of this week and get back on a regular schedule for publishing my 'Excel in Seconds' and 'Excel in Minutes' newsletters.  I hope you like my 'Excel in Minutes' tip this week and are kind enough to share and recommend it to your colleagues and any other Excel users you know. Again, I apologize for the inconsistent publishing schedule of my newsletters. Thanks again for your patience. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
|
| Fill The
Gaps In Imported Data |
|
When you bring data into Excel from another source (or copy data from a Pivot Table), oftentimes some of the cells are left blank, indicating a repeating value from above. This may make the information easier to read but it also means that you cannot use many of Excel's best features, such as filtering, subtotaling and Pivot Tables to manipulate this data.  If you have only a small amount of data, you can simply fill down (CTRL+D) or Copy and Paste the values for each blank section. However, when you are dealing with hundreds or even thousands of records, those methods are not practical. Today I want to show you a 'fairly simple' process to help you complete this task in less than a minute, regardless of how many records you are dealing with. In this example we are going to fill all the gaps in columns A to E…  1) Select all of the data, both blank and non-blank cells, in a single range (e.g. A1:E22);  2) Press the F5 key on your keyboard and click the Special... button in the 'Go To' dialog. Then select the Blanks option in the 'Go To Special' dialog and click OK;  3) Now, only the blank cells are selected;  Notice that the 'Active Cell' is now A3.  4) Next, press the equals sign (=) key, then press the 'up arrow' key once. This will create a formula reference to the cell immediately above the Active Cell;  5) Now hold down the CTRL key and press Enter. This will fill each of the selected (blank) cells with a formula (e.g. =A2) that references the cell above itself;  6) Next, re-select the entire data range again and move your mouse pointer to the edge of the selected range until it changes to a 4-headed arrow (see image below); 7) Press the right mouse button and drag the cells a little to the right, BUT DON'T RELEASE THE MOUSE BUTTON. Still holding down the mouse button, drag back to the original location and release the button (see image below). 8) Click the 'Copy Here as Values Only' option from the menu that pops up to replace the formulas with fixed values.  Now all of the previously empty cells are filled with the values from above and your data is ready for sorting, filtering, subtotaling, etc...; Go ahead. Open this practice file and try it for yourself now... It's easy. |
|
|
|
|
To share this tip with your friends and
|
|
|
|
|
 |
|
|
|
|
 |
|
| If you would like to share this
newsletter with others...
1) Forward this
newsletter by email, but first delete the
unsubscribe link at the very bottom so you don't
get accidentally unsubscribed
2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com 3) Post a link to TheExcelAddict.com in a company newsletter or website. Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. |
|
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |