Hi and welcome back.
I know I have been a bit quiet on the blog front recently, but that is just because all my other fronts were demanding my attention! But I missed keeping in touch, so I am back with a new series of Excel blogs. They are going to follow my father's rule of keeping everything "Short, sweet and to the point".
So these missives will be shorter than my previous posts, but will hopefully still be as useful. And they will be all about getting back to basics. Knowing all you can about the easy stuff in Excel so you can practise it, make it perfect, then not even think about it while you use the easy stuff to do the hard stuff.
As always, if there is any little (or big) issue in Excel you would like me to address, please feel free to leave a comment or send an email through this link.
In the first few blogs in the "Back to Basics" series, we are going to get to know the menu ribbon. I will randomly select different icons off the ribbon and tell you what they do. You should hopefully learn something interesting each time.
Our first icon looks very unassuming and innocent but is probably one of the most powerful icons on the ribbon. It's this one:
Yup - that tiny little arrow you may have thought was just window dressing, actually allows you to add any tool you want to your Quick Access Toolbar, which is the top line of icons above the menu bar. Why this is handy is that you can add any tools you use frequently to the Quick Access Toolbar, and then you don't need to scroll though your menus to find them.
You can also add tools that aren't necessarily available on your menu. By clicking on the icon circled above, you can select the option More Commands... and you will get this:
Choose the type of commands you want to look through from the dropdown selection at the top left hand side, click on your selected tool icon on the left, click Add, Ok, and you're done.
You might notice this little icon I have added to my own Quick Access Toolbar:
This little gem is a wonderful time saver. Click on this, and it will open a list of your recent workbooks and places, making finding files you have been working on a lot easier. It also allows you to "pin" a workbook so that workbook always appears at the top of your list. Even if you haven't opened it in months. Just click on the little picture of the thumbtack to the right of the workbook name, and you're sorted.
How to add it is simple. Once your have your "Customize your Quick Access Toolbar" Menu open, select All Commands from the drop down list, and then go to the command "Open Recent Files" (the commands are listed alphabetically), and add it.
So, there you go. Short and sweet, and 2 little handy tips to brighten your day!
Have a great one!
There is a company in South Africa that has been around since I was a just a sprog. It is called Nashua and provides business solutions in terms of printing equipment. And it's tagline has always been "saving you time.saving you money.putting you first".
This has to be, in my mind, a better tagline than "Just do it" or "I'm loving it". Because it has stuck in my mind since I was a small child, and every time I think the words "Saving you time", the rest of the tagline immediately pops into my head.
So yes, its a brainworm. But its also a tagline that makes sense, because saving time in business almost always equates to saving money. So here is my way to "put you first". I am going to share my top 5 time-saving tips for Excel.
Yes, some of these are basic, but its normally the basics we do everyday...so save time on those and you'll have more time for the fun stuff.
Tip 1 - Learn your shortcut keys for the tasks you do most often.
Shortcut keys are basically a specific keyboard sequence you press, normally whilst pressing Ctrl or Alt, to perform a specific task. So for instance, the shortcut key for Print is simply Ctrl P.
To add filters to a page, you would hold down Alt and push D then F then F.
Now, instead of giving you a ridiculously long list of shortcuts, you can find out your favourites on your own. The best way to do this is to work your way through the menu ribbon as you normally would, but push Alt before you start.
Here is an example...my all time pet hate of gridlines, and how to get rid of them. So first off, click on your sheet, in the right cell if your task is cell specific, and then click Alt, and you'll get all these little letters in boxes all over the menu bar, like so:
Click on W for View, and more letters will pop up, as below:
As you can see, you need to press VG to either untick or tick the gridlines box. Now, you can follow the letters each time you do it, but if it is something you do regularly you will soon learn the sequence, and will type in ALT W,V,G automatically. Practice forms habits, and this is a good one to learn!
Two side points:
1) If you have a dropdown list in your menu, look at the underlined letters in each selection - those are your shortcut keys.
2) As you can see from my first image, the quick access toolbar at the top has numbers assigned to each icon. Again press Alt, then the number, and the specific item on the quick access toolbar is activated.
Which leads me beautifully to my next tip:
Tip 2 - Add your favourite menu items to the Quick Access toolbar.
If you're not strong enough just yet to break the bond with your mouse for a full time relationship with your keyboard, then use the Quick Access toolbar. That is the thin strip across the top of your menu ribbon, and you can add whatever menu items you like to it. To do this, simply click on the little black downward arrow icon to the right of the bar, and you'll get a dropdown menu like this:
The most basic are provided for you to pick, but if you want more choices, click on "More Commands..." and you'll get pretty much every menu item you can think of. Click on the one you want and press add, and you'll see the menu icon up on your Quick Access Toolbar. Again, easy stuff, but will save you scrolling through menus.
Tip 3 - Use your autofill to create lists
If you are creating a list of, say, months, instead of typing in each month in a new cell, just type in January in the first cell, February in the next, and then highlight both, and drop your mouse to the bottom right hand corner. A little black cross will appear. Drag this down and your list will appear automatically.
The little black cross also works effectively for filling in lists. You don't even need to drag it down. So imagine you had a formula next to your newly formed list of months. If you type your formula into the next column on the first cell, you can again go to the right hand corner, and double click the little black cross. This will copy down your formula to the bottom of your list.
A big time saver!!
Tip 4 - Always use formulas where possible
Imagine you had a complicated spreadsheet calculating various cost and sales prices based on specific VAT rates and exchange rates. While you were creating your spreadsheet you happily typed in your formula, for example = (B4 x C4) x 0.14, because you assumed your VAT rate would stay at 14% forever. But the taxman is nothing if not fickle, so when, not if, he changes the VAT rate, you would need to change each formula manually (or at least use a FIND and REPLACE function).
If you set up your spreadsheet from the start to have your VAT rate in one cell, and you referenced all your formulas to that cell instead, you would only need to change one cell and all your formulas will automatically be updated.
I know it sounds simple, but it is such an easy mistake to make because when you are creating a spreadsheet you are normally focusing on the output and not too much on the design. Try get into the habit of stopping at each new formula you write and say "Have I made this as easy as possible to adjust in the future?".
Tip 5 - Get to know your Paste Special options
Most people know about Paste Special to paste something as values. But this lowly little box of options has so much more to offer, so take the time to get to know it.
These are fairly self explanatory, but I'll go over my favourite ones:
Formats - does what it says on the tin - another way of copying formats of cells from one spot to another.
Multiply - this may sound strange, but I use this as a nifty trick to change data from text to numbers. What you do is type a 1 somewhere else in your spreadsheet, copy it, and then highlight your list of text and Paste Special -Multiply. This will multiply the text numbers by 1, turning them into numbers you can actually use.
If you ever need to take your numbers into thousands, you can do the same thing, just type in 1000 into a cell and Multiply it across your range. You can also Divide by 1000 to bring your figures into more reportable size numbers.
And what if you create a lovely long list of items going down your sheet and then realise that you actually want them going across your sheet as headers? Easy, just highlight them, Cut them, click on the cell where you want them to start going across, and then Paste Special - Transpose.
That's a massive time-saver all on its own.
So there you have it. My 5 top tips on saving yourself time, and hopefully money, whilst using Excel.
Why not share one of your favourites on the comments below? I'd love to hear them.
Don't want to miss out on the blogs? Why not sign up and we'll send you a friendly email to remind you!
Happy Spreadsheeting!
