EXCEL – BURNING QUESTIONS
1. Why is a New Excel Workbook called ‘Book1’?
2. How do I avoid losing 3 years of my life looking for the ends of a worksheet?
3. Excel Oh Excel, how many are the ways I save thee
Why IS a New Excel Workbook called Book1?
OK, every Office document, not just Excel, has something called a ‘Normal’ Template.
This means every time you open the application, in this case Excel, it throws a copy of the ‘Normal’ template into a new blank workbook, that’s why the font size type and other things are what they are (you can change the Normal template but that’s a story for another day…).
It calls it ‘Book1’ as Excel files are referred to as workBOOKS. The 1 on the end indicates that this is the first new Book that you have opened since opening Excel itself. If you open another one without closing Excel it will call it Book2, another one Book3 and so on. If you close Excel and open it again it goes back to Book1. These files in Excel Desktop (as opposed to Excel Online) are unsaved until you save them, when you hit save they will always prompt you to give them a file name.
DON’T MAKE the filename Book1 etc as that doesn’t give you a Mr McGoo what is in the file and on top of that it has the potential to be flipping confusing…
How do I avoid losing 3 years of my life looking for the ends of a worksheet?
There are 1,048,576 rows in a worksheet. That’s a lorra lorra rows. Very handy to know from the Get-go when creating and setting up your workbook.
It’s therefore easy to wander down there without realising, type some stuff, close your workbook, open it up and then think you are going mad because you are looking at the first twenty-odd rows on your screen and there is NOTHING THERE! You start scrolling down, then you scroll some more and then some more and still no joy so you just assume you dreamed it and re-do all your work.
You can avoid this pain by taking a deep breath on opening and finding yourself at a blank A1 where you ‘thought’ you had started typing your data, press the Ctrl and Down Arrow on your keyboard and if there is anything in Column A your cursor will find it, if there is anything before that it will find it first and you can just keep pressing Ctrl and Down Arrow until you get down to row 1,048,576 in less than a second. You could of course search for a keyword with Ctrl and F but we’ll cover that in more detail in a future edition
Ctrl and Right Arrow does the same as Ctrl and Down Arrow but across all the columns until it reaches column XFD which is the 16,384th row in a worksheet.
That is a lot of dance floor to get lost in so it’s handy you know where the ends of your worksheet earth are, not just for this reason but for others too I’ll tell you another time…
Excel Oh Excel, how many are the ways that I save thee?
There are LOADS of different Excel file types because there are loads of different things that they can do.
If you want to release your inner Nurd in a darkened room then you can check them all out here;
https://docs.microsoft.com/en-us/deployoffice/compat/office-file-format-reference
If not however, the main ones to look out for are listed below as well as where you might use them;
.xlsx
Excel Workbook
The default format for Excel 2007 onwards. Can’t store VBA macro code.
.csv
CSV (Comma delimited)
Saves the data in each cell separating it by comma instead of in cells. Saves only the active sheet. Quite often the format in which other software applications will spit out their data for you to mash up in Excel as you see fit.
.pdf
PDF
Portable Document Format, a format that preserves document formatting and enables file sharing. If you create your invoices in Excel and then save them in a pdf it makes it more difficult for your customers to edit them and change the amount or some other such skulduggery. It also means if they are accessing the information via a route that might not display the workbook as intended, it stops it from ‘looking weird’.
.txt
Text (MS-DOS)
Saves a workbook as a tab-delimited (that’s data then hit the tab key then more data to you and me). Saves only the active sheet. One of the ways to reduce heartache when you are trying to shove said data (import) into another application.
.xlsm
Excel Macro-Enabled Workbook
If you are going to get all hot and heavy with automation in Excel and use macros, you need to save your workbooks as this.
You’ll find the list of different types to pick from in the ‘Save As Type’ dropdown list when you select the ‘Save As’ dialogue box when saving an Excel file.
For a Billy Bonus….
These last two file types are if you have created a thing of beauty which took you forever, a little Excel Sistine Chapel if you will;
If you want to avoid getting thrown in jail for murdering a co-worker who does bad things to your precious work, then templates are the saviour for you.
If you save (anything in Office for that matter) as a template, when someone selects it from the ‘personal’ link when going to ‘New’ or from a shortcut on your desktop for example, it will just throw a copy of itself into a blank workbook (with a little 1 at the end just like Book1 which is where we started) and they can then do all manner of damage and your masterpiece is protected and they are saved from a painful death.
.xltm
Excel Macro-Enabled Template
The macro-enabled file format for an Excel template for Excel 2007 onwards.
.xltx
Excel Template
The default file format for an Excel template for Excel 2007 onwards. Can’t store VBA macro code.