CUSTOMIZE YOUR EXCEL!

Excel 2010.

In Microsoft Excel's latest version released to date (version 2012 is coming out soon), Microsoft has made the world's most popular spreadsheet application even more customizeable. We'll be the first to say that there is still plenty of room for improvement, but in terms of reasonable progress, they've met our expectations.

1) Excel Color.

Your usual trio of 3 choices present since the 2010 release is still available. We would have preferred a more customizeable selection, but we certainly understand why the developpers didn't want to give a blank canvas to the internet trollers out there. Changing your "boring" and so-overdone blue hue into either Grey or Black (it's still grey, just slightly darker, see image above) is simply, yet not easy to find.

1. Go to File, Options
2. Change Color Scheme

and make your choice.

2) Quick Access Toolbar (QAT) [pronounced cat]

Similar to the previous version, Excel 2012 allows you to add any button to your QAT. By default, you've got your Redo and Undo buttons there, as well as your Save.

Here's where you can add some quick-access buttons (hence the name). Here are our favorites:

a) Paste Values

You've used this option a lot so far, even if you're an Excel beginner. This allows you to "kill" any formulas and paste only the result. i.e. if you've got a =Today() function, and you don't want the value to change tomorrow, simply copy and paste-values. This paste-values button can be added to your quick access toolbar to save time.

(Note: In 2012 only, Microsoft have added a quick menu that appears when you copy-paste a cell and then press ctrl or move your mouse over it. This menu contains all the paste-special options too).

b) Toggle Read-only

If you have ever worked on a file, and a coworker has opened it already, or wants to open it simultaneuously, you're comfronted with the obvious dilemma of whose changes get to stick, and who opens the file as read-only. By default, the first user opens the file as read-write, and subsequent users open it as read-only. If you want to have more control over this, you can close your read-write file, and open it as read-only (using the Open Menu Options button). It's always tedious to have to close and reopen the file, or rather remember to open it as read-only in the first place. That is why this "Toggle Read-Only" button can come in handy. In addition, this button is found no where in the Excel standard menu ribbon. Here's how you can add it yourself

1- right click on the QAT (next to the undo button), click on Customize Quick Access Toolbar...
2- From the top drop-down menu, select All Commands, and find "Toggle Read Only" then add the button.

you've now got the toggle read-only button on your QAT, go ahead and try it on a file, it's great!

3) Customize Ribbon

Exclusive to Excel 2012, you can now modify your ribbon. In the same menu as above (right click on your ribbon) click Customize the Ribbon... This will give you all the options you need in order to make your ribbon yours. You can position toolbars around, even create your own ribbon piece, with your favorite Excel buttons, or even your own User-Defined macros.

(Note: Whatever Macro you create and save in your Ribbon must be saved to your PERSONAL.XLS file for it to work next time you open Excel. You'll have to copy the Macro manually (or export/import) onto other computers' PERSONAL.XLS file in order to transfer it to other users).

Microsoft Excel Navigation using the keyboard only

2D Canada Inc. presents: Free Microsoft Excel Tips and Tricks for the average & advanced users!

1. Easy Tricks:

 Navigating through your spreadsheet with your keyboard. A few must-know ways of navigation involve using your keyboard for quick and easy scrolling, selection, and activations. The main principles are 1) scrolling and 2) selection. Most of the time, users need to select a range of either: 

• single cell
• multiple adjacent cells
• entire row(s)
• entire column(s)
• non-adjacent cells

For each of the above selections, the user must first navigate to the start of the range.

Single cell selection is straight forward, as long as we can get to that cell. Without using the mouse, the main keys to remember are : CTRL, SHIFT, SPACE, and the obvious ARROW-KEYS. A combination of these keys will select any and all cells in a spreadsheet, without using the mouse.

CTRL+ARROW KEY. The control key is used to skip or jump to an 'end'. The End means the last non-blank cell in a range. If you are in the middle of a row of data (remember, a row is horizontal, a column is always vertical), that is to say not at the very end of the row of data, and you hold down the control key and press ARROW-RIGHT, you move to (select) the last non-blank cell in that row.

Try it: Type a series of numbers in a row starting in A1 using TAB after each entry. Type 1 > Tab > 3 >Tab > 6 > Tab > 7 > Tab > 23 > Enter. Now cell E1 should show 23.

Now select cell B1 (3). You are inside a range of data. Holding down CTRL, press ARROW KEY RIGHT. You will jump to the end of the range. If you now hold down CTRL and press ARROW KEY LEFT, you will jump (not back to B1, but) to A1, or the opposing end of the range. You can try this with a range of data in a columnar format too.

Notice what happens when you're already at the end (E1) and press CTRL + ARROW RIGHT again. You jump to the end of the next range, which is blanks, and hence, the last column of the file. You can press CTRL + LEFT to jump back to your active range.