#1. Manual Calculations
Excel is rather like that clever kid at school, you know, the one who could sums in his (or her) head and somehow manage to keep the answers there without being distracted by a passing seagull. Nevertheless, there are times when even Excel can get its knickers in a twist, those times usually being when there’s thousands of calculations going on in real time; after a while the grind begins to show, and Excel slows to the mathematical skills of a three year old. The solution to this is to disable automatic calculation, and here’s how:
Click on the ‘Formulas’ tab in the Ribbon. In the ‘Calculation’ section, click on the down arrow under ‘Calculation Options’.
In the drop-down menu, select ‘Manual’.
When you input data, you will now have to press ‘F9’ to update the calculations, and Excel will have a sudden spurt of life.
#2. Copy As Picture
Copying a chart, graph or a set of cells to an application outside of Excel can have some disastrous effects. Formatting goes all wonky (a technical term), the sections of the nicely presented 3D pie chart end up looking like the left-over’s from the Micro Mart lunch trolley and you begin to foam at the mouth. Never fear, though, simply consider copying the item as a picture instead.
Select the chart, graph or cell range.
In the ‘Home’ tab, click the down arrow under ‘Paste’.
Scroll down the drop-down menu until you reach ‘As Picture’, then extend the sub-menu and select ‘Copy As Picture’.
You can now paste into other applications without fear of bringing down the wrath of the formatting god.
#3. A Change Of Order
Normally, when entering data into Excel after pressing the Enter key you move down to the cell below. This is great for when you’re entering data in a column, but what about when you need to enter data in rows?
Click the Office button (the round button in the top left). Click on the ‘Excel Options’ button at the bottom of the new window. Click the ‘Advanced’ link, and using the drop-down menu under the first item, ‘After pressing Enter, move selection Direction’, choose from Up, Down, Left or Right.
#4. Custom Auto-fill
The Auto-fill function is great for completing rows or columns of sequential data; simply enter the first two values into the initial cells, select the cells and drag the fill handle to extend the rest of the cells. But instead of just 1, 2, 3, and so on, you can add your own custom lists by doing the following:
Again, click the Office button, followed by ‘Excel Options’ at the bottom. Select the ‘Popular’ link, and in the first section click on the ‘Edit Custom Lists...’ button. Select ‘New List’ from the left window, and click in the ‘List entries’ box.
Start typing your new sequence, pressing Enter after each entry. When you have finished, click on the ‘Add’ button, followed by ‘OK’.
There you have it, a custom sequential Auto fill list ready for you to use.
#5. Creating A Named Range
You can quickly create a named range for a group of cells by doing the following:
Highlight the cells you want named and click in the left of the Formula bar, and start typing in a name for the range.
The named range for those group of cells is now assigned and can be called via macro, or other function.
#6. Developer Ribbon
If you’re into playing around with macro’s and form authoring tools, then you’re going to need to activate the Developer Ribbon. It’s really easy to do, and offers a wealth of otherwise hidden options. Here’s how:
Click on the Office button, followed by ‘Excel Options’. Select the ‘Popular’ link, and in the first section, tick the ‘Show Developer tab in the Ribbon’ box.
And there you have it the Developer Ribbon, containing Form Controls, - ActiveX Controls, XML Source and a Visual Basic Editor. Not for the faint of heart, but interesting nonetheless.
#7. Add Hidden Features To The Quick Access Toolbar
Excel has a number of extra hidden features that are, by default, invisible to the Quick Access Toolbar - which is that bit along the very top of the Excel window. To include any, or all, of these hidden features, do the following:
Click on the Office button, followed by ‘Excel Options’, then select the ‘Customize’ link (American spelling, I know!).
In the drop-down menu, ‘Choose commands from’, select ‘Commands not in the Ribbon’ and pick the commands you’d like to include in you customization, followed by the ‘Add’ button in the middle of the two screens.
Besides creating new icons on the Quick Access Toolbar, adding these can help improve your productivity somewhat. Or, at the very least, give you access to a more personalized Excel experience.
#8. Clipboard Pane
Another helpful boost for productivity is activating the Office Clipboard Task Pane. Once launched, it sits in the left hand side of the Excel window and gives you access to the entire contents and history of the clipboard, which is handy for multiple copy and pastes.
Select the ‘Home’ tab in the Ribbon then, in the ‘Clipboard’ section, click on the little diagonal arrow, located in the bottom right of the section. This will launch the Clipboard Pane and give you access to the set of options that lie within.
#9. Clever format painting [Excel 2003]
Even if cells don’t all have the same characteristics (some might be bold. others italic, some one colour, others another) you can still highlight them all then open the Format Painter tool (it looks like a brush) and ‘paint’ those multiple characteristics onto another row.
#10. Wrap text in cells [Excel 2007]
Type in the over-long text, click the Home tab and select the cell, then find the Alignment section of the ribbon and choose Wrap Text.
#11. Re-use a chart style
Select the Finished chart and click the Save As Template button. Make sure the Charts folder is selected, name it and click the Save button.
#12. Hide a worksheet
Right-click on its tab at the bottom of the screen and choose Hide from the pop-up menu. To restore it, right-click any of the other tabs, choose Unhide and select the relevant sheet from the list.
#13. Conditional formatting
Create a dummy worksheet, make sure the Home tab is selected and open the Conditional Formatting drop-down menu.
Choose Highlight Cells Rules and select Less Than.
Type 50 in to the first box and leave the other settings as they are. Click OK.
Whenever the contents of a cell fills below 50 it will be highlighted in red.
#14. Recover unsaved workbooks
Open the File menu, choose Info, then open the Manage Versions drop-down menu and select Recover Unsaved Workbooks. You may be able to restore an unsaved workbook from there.
#15. Add mini-charts to worksheets
Highlight the cells containing the data and click the Insert tab.
Find the Spark-lines section on the ribbon and choose a chart type.
When the dialogue box opens, confirm the data range is correct and select the reference for the cell where the spark-line should appear. Click OK.
#16. Check for errors
Setting up a spreadsheet for others to use? Select the cells the users type into, select the Data tab and click Data Validation. When the dialogue box opens, choose the relevant value from the Allow list, click the 'Error Alert' tab and type in your personalized error message. Click OK to close the dialogue box.
No comments:
Post a Comment