Microsoft Excel is an awesome tool for organizing and figuring huge amounts of data. With plenty of useful features it offers it is easy to get lost especially for a non-expert or a newbie. So here are 12 useful, easy-to-implement tips for you to take advantage of (as long as you don’t lose your excel password):
1. Switch between multiple Excel windows using FireFox-like tabs:
OfficeTab (via MUO) is an awesome tool that gives FireFox feel to Office applications. With it you can switch between active Office apps within one window which is much more convenient than having to hit the task bar. And if you set it to work for Excel only, you will get a much handier way to switch between multiple open Excel docs. The nifty features include:
- Opening the new tab (i.e. new Excel doc) by simply double-clicking the area where the new tab would appear (just like in FireFox).
- Marking unsaved docs with a star (*) for you to see which docs you need to further work on.
2. Apply formats to a range of cells depending on their value
To apply formats to a range of cells depending on the value of the cell or the value of a formula you will need to use Excel tool called Conditional Formatting (Example: you can make a cell appear bold only when the value of the cell is greater than 10). Here’s a detailed tutorial on how to use the tool.
3. Filter out and get rid of duplicate data
To filter out cells with duplicate data, go to “Data” menu, select “Filter“, then Advanced Filter, click “Copy to new location“, leave the “Criteria” field empty, and make sure you have checked “Unique Records Only” (via)
4. How to analyze your data using Pivot tables
What’s the best way to learn to use complex Excel tools like Pivot tables? By examples, I guess. Here’s a great tutorial which shows how to use Pivot table to re-arrange and analyze data by 4 actionable examples.
5. Create a linked picture of a range
Here’s a great tip on using a hidden Excel option of creating a live “snapshot” of a range of cells and placing it anywhere you like.
To find this option:
- Select a range of cells
- Choose Edit, Copy
- Hold down the Shift key, and then choose Edit, Paste Picture Link.
The result is an image of the selected range that will reflect any subsequent changes to the source.
6. How to filter data by multiple criteria
DigDB (paid with free trial) is a cool add-in for Excel that makes it easier to process huge amounts of data.
7. Search Excel newsgroups with Google.com right inside Excel
This add-in allows to search directly from within Excel the Excel newsgroups via Google.com for answers to questions you may be wrecking your head over.
8. How to import a text file into a spreadsheet
To import a text fil into a spreadsheet, select Data, then Import External Data, Import Text File. In the dialog box select a text file, in the Text Import Wizard specify how Excel should import the data. Finally, in the Import Data dialog box, you can specify the desired worksheet destination (via).
9. Create an in-cell chart to illustrate a percentage
Here’s a great guide decribing how to create in-cell charts in column C that will illustrate the percentage in column B. Moreover, when the value in column B is changed, the chart will be adjusted accordingly:
10. How to insert in-cell line break
There are many people who have been using Excel for years and still don’t know how to insert a line break within one cell (as “Enter” changes the active cell). The solution is simple: press Alt-Enter to insert a line break.
11. Create automatic list numbering
Here’s an easy way to make Excel add numbers to your list automatically (ignoring the empty rows). Just grab this formula:
=IF(B1<>"",COUNTA($B$1:B1)&"."
and copy it down to
(all) cells in column A. The formula displays the next consecutive item number if the corresponding cell in column B is not empty.
12. Have a picture pop-up when you hover over a cell:
Really simple, but many people are just anaware of this option. Comments pop up when you hover over the cell, so you just need to insert an image in the comment to make a picture pop up on mouse-over:
- Right-click on a cell and insert a comment.
- Left-click on the diagonal lines surrounding the comment to changes the border to dots
- Right-click on the dots and choose Format Comment
- Go to tab called Colors and Lines
- In the Color dropdown, choose Fill Effects
- Choose Picture and then Select Picture
- Choose the picture and click OK
Popularity: 48% [?]
{ 1 trackback }
{ 0 comments… add one now }