Excel - Fill in blank data quickly using Ctrl-Enter

Introduction
I had a list (data set) in Excel where there were multiple rows that were similar but missing data in one column. The list was very large so filling in the data by hand would've taken a long time. However, there's a little trick you can do to make Excel fill in data quickly. You must use the Special Search to find the blanks and press Ctrl-Enter. Using a simple table/list of towns and counties in England I'll demonstrate how this works step-by-step:


Instructions
The following screen shot shows a list of data. There are columns for towns in England. In column B the county name is listed. However, for the counties of Bedfordshire and Berkshire we have blank cells. We know that the towns below them are in those counties; Ampthill to Woburn (A18) are towns in Bedfordshire. Ascot (A19) to Woodley (A34) are in Berkshire.

In the above the blanks should be filled in. Cells B3 to B18 should contain the word "Bedfordshire" and B20 to B34 should contain "Berkshire". 

The first thing to do is select the blank cells:

Click Find & Select

Click Go To Special

Click Blanks

Click OK

All the blank cells are selected:

With the blank cells selected (as shown above) click into the formula bar at the top - see the screen shot below (be careful though, don't click on anything because the selection will change and you'd have to start the steps all over again):

Type the cell reference of the first filled in cell. For example, above I typed in the formula bar:
=b2

Press Ctrl-Enter  

You'll see the result, all the cells are filled in - as shown in my example below:

You'll notice that Excel is clever enough to realise that the cells B20 to B34 also had to be filled in but using the cell B19 - so Berkshire is correctly entered for those cells.


Conclusion
This is really useful, thanks Microsoft! If you have a report generated from a business intelligence or reporting tool, sometimes when you import it into Excel you have missing values like this. It can happen because of formatting in your report. In Excel you need all the cells filled in so that it's possible to use a pivot table. The above steps can save you hours of copy/paste work because Excel will fill in all the cells for you.


Excel Version?
It works for 2013 and 2010, probably earlier versions too.


Thanks
Many thanks to my colleague Claas who enlightened me to this little Excel trick!


Reference
I found the sample data used in this example here:
http://www.sp-tech.co.uk/resources-towns.php

Windows 8.1, Hyper-V, disk thrashing and ReadyBoost

Introduction
I have an Intel Core i5 with only 4GB RAM. Windows 8.1 64-bit runs very well on it as it is. However, I installed Hyper-V and a Windows 7 64-bit virtual machine (allocated 1GB initial RAM but it can use as much as it likes dynamically). With the virtual machine running the computer was thrashing the disk drive constantly! When looking at the Task Manager usually the Disk activity was at 100% or near it. I was lucky when it dropped to 80%! This made the computer very slow to use, especially when I tried to work inside the virtual Windows 7 guest.

I knew the ultimate solution was to increase the physical RAM in the computer but I didn't have any to hand... what else could I try?

I ran the disk Defrag but there was nothing to de-fragment! I checked the paging file but that was fine, it was set to automatic (I know some people say it's best to set it to something manually but in my experience it makes little difference - Windows 8.1 is much more clever than Windows 95!).


ReadyBoost
I then remembered something I'd looked at many years ago when I first had Vista. I remembered ReadyBoost. When Windows Vista was released it was touted as a way to increase the speed of your computer without adding RAM. The idea is that you plug in a USB flash drive (SSD - Solid State Disks). The computer can read small files from flash memory faster than it can read from the hard disk drive. This can improve performance as Windows is often reading small system files. Back in the days of Vista I had tried it but I'd not found much of a performance boost. But I decided to give it a go, maybe with Windows 8.1 it will make a difference?

I had an old USB 2.0 flash drive, a 'Disk to Go Fusion' 16GB. I plugged it in, formatted it to exFAT. I right clicked on it in File Explorer, clicked ReadyBoost - I dedicated the entire USB flash drive to ReadyBoost (see the screen shot).

I shut down and restarted the computer (this is important, until I did this I saw no improvement).

Once Windows 8.1 was back up and running I tried my Hyper-V Windows 7, I used my PC for a couple of days in this configuration. There was definitely a change in performance!!! When viewing Task Manager the Disk dropped down to 80, 70 and lower. Windows 7 was still not 'fast' but it was usable. It worked!


Ultimate
I added another 4GB RAM and now my PC is running even more smoothly, the Task Manager reports very low disk usage. This is the obvious choice.


Conclusion
Upgrade your RAM! But if you do have a spare USB flash drive, try ReadyBoost. It can speed things up, at least to the point where you can work with the computer.

I still have the USB flash drive plugged in and according to the Windows 8.1 Performance Monitor, it is working... The following is a good blog article I found that explains more about monitoring:
http://blogs.msdn.com/b/saveenr/archive/2009/05/02/monitoring-readyboost-on-windows-7.aspx



Reference
Monitoring ReadyBoost
http://blogs.msdn.com/b/saveenr/archive/2009/05/02/monitoring-readyboost-on-windows-7.aspx

ReadyBoost on Wikipedia
http://en.wikipedia.org/wiki/ReadyBoost





NOTE: What I've described here was not a scientific test of ReadyBoost. I am just sharing my experience just in case it helps someone.

Excel - Custom Filter in Pivot Tables

Introduction
There are times when you may wish to filter data in a pivot table. It's easy enough, click the corner filter button and select your filter. You can also select multiple items to filter. But if you want to select multiple items based on some criteria, how do you do that? In a normal (non-pivot) filter it is possible using a Custom Filter to use AND/OR. But in a pivot table there's no Custom Filter option.


Solution 

1. With your pivot table on the screen, click the data you want to filter on
2. Click the filter button (top right hand corner)
3. In the Search box enter the first text you want to search for
4. Click OK - the filter will work, it will show you only the results based on this first text you are looking for
5. Click again on the filter button (top right hand corner)
6. In the Search box enter the second text you want to search for
7. Click the option Add current selection to filter
8. Click OK

The result is that your pivot table data is filtered on two criteria. You can continue to filter and click the Add current selection to filter if you need to (it is the equivalent of <something> OR <something> OR <something>, etc).

That's it!

Excel Version?

It works the same in Excel 2013 and 2010.