On our way.
Well, as you will be able to see from the image above – the basic spread sheet that Malcolm wanted is built. To save a bit of time I’ve cheated and populated it with a copy of the United States Civil Aircraft Registry, it saves me time in two ways. Firstly I already had it downloaded, secondly I had broken it down into over thirty data points per registration from which I only had to extract eight and import them into to my spread sheet.
At a basic level, this also shows something of the power of Excel. I have added over three hundred thousand rows of data, each with eight data points – so this spread sheet is handling almost two and a half million data points and it is running quite happily on a fifteen year old MacBook. This series of posts is just to indicate that this is a viable option for logging, it is in anticipation of you moving to something else. But it should be remembered that Excel is a perfectly good tool for this and there may not be a requirement to move.
You will see that there are some minor embelishments, these enhance the functionality and make Malcolms life easier. The first of these is the Auto Filter function, this is a really cool mechanism common to nearly all spread sheets. It allows you to make the best possible use of the data, effectively you can look at it any way you want. Now primarily we are just going to use Excel as a logging tool, where we can keep our data until we decide if there is some place better.
Now there are lots of different tools available in Excel, I’ve deliberately chosen a nearly 20 year old version to cover as much of the spectrum as possible. Any features that I demonstrate will be inherent in later versions, so no matter which version you have this will all hopefully work.
Setting up your header row.
So whether you have five columns or twenty five columns, to make life easier we have to do a little configuration in Excel. To keep things simple, there are only two changes to the configuration that we need to make at this juncture. Now I’m not going to cover anything difficult here, the two changes that we have to make are setting the Auto Filter function on and freezing the first row (The one with the Column Headings in it). This gives us the flexibility to filter by any data point, it also ensures that the filter mechanism is visible at all times.
First we’ll cover what the Auto Filter delivers, you may not need or want this functionality. I find it to be a real bonus, but it might be that it doesn’t suit what you want to do with your log.
Setting this Auto Filter function is very simple, at the extreme left we se a column with the row numbers. If we select row one to highlight the row, then go to the data tab and select filter then select Auto Filter and that is it. You have now enabled one of the most powerful Excel functions.
Excel Auto Filter is for me one of the best features, it allows you to home in on specific data just so quickly. Now as I said I have pre-loaded Malcolms spread sheet with more than three hundred thousand rows, scrolling up and down can just take so much time. So using the Auto Filter can reduce this by so much time, so here we go with a quick example.
We are going to look for aircraft with a 100th birthday this year, just to see how many there are. To do that, we hit the dropdown at the right side of the Build Year box.
This presents us with a drop down, giving all the listed years in the column – so if you had ten different years that is all that would show.
We scroll down and select 1926, a full hundred years ago – this will give us a list of aircraft that are exactly one hundred years old this year.
Just hitting the enter or return key will select the rows from the spread sheet that match, it doesn’t get any more functional than that. To return to the standard display, just select the drop down again and select (Show All).
Now you can select multiple filters, for arguments sake I could have selected on Manufacturers Name after Build Year. This would give ne a drop down with only five entries, selecting “RYAN” would leave me with only two rows.
This post is starting to get a little unweildy, it is larger than I like. So anything not covered in this post will be covered in the next.
The next post.
So the next post will cover Freezing the panes so that the top row will always stay where it is, meaning that the headers will always be visible. We will also touch on searching for something, both in the Auto Filter and a free search across the spread sheet.