Microsoft Excel has a wealth of practical uses just waiting to be discovered. Some of these uses are not well known while others are used on a daily basis. To master this powerful application, we recommend looking into Microsoft Office tutorials. But to get you started, here are a few basic Excel tricks we found to be extra helpful.
Sending a Read-Only (PDF) File
There may be a time when you need to make a report that can't be manipulated or edited. This is where PDFs come in handy. But if you don't have Adobe Acrobat, you can use Excel to create your own PDF file. When you click File > Send you can Email as PDF Attachment. You can also save the file in PDF format. This enables you to create PDF-like files that aren't editable. If your current version of Excel doesn't have this option, you can download the PDF add-in from the Microsoft website.
Importing Data From a Website
If you come across a site with loads of data that is useful for a project you are working on, you can convert this into a spreadsheet. Click File > Open then change the file format to All Web Pages. This covers all .htm, .html, .mht and .mhtml files. Load the web address and click Open. Once you've done this, you may need to alter the formatting, but this is much easier than creating a new workbook.
Filtering Your Results
Let's say you have a large spreadsheet with loads of information on it. Using AutoFilter to filter data is a quick way to find and work with a subset of data in a range of cells and it lets you choose what you want to see and what you want to exclude. Click the cell in the top left corner, highlighting the whole spreadsheet. Click Data > Filter > AutoFilter and you will see there are now little boxes in each column that help narrow your search. You can also use a Custom filter and just type in the information you want to locate.
Creating Call Logs
One of the most useful features about Excel is the ability to organize data. Being able to organize names or clients is useful if you spend a lot of time making and receiving calls. It's very simple to create a call log using Excel and easier than trying to use Word. Create a worksheet with three simple headings: Name, time of call and reason for call.
Color Coding for Emphasis
This really isn't a secret, but there are many times when a spreadsheet can be improved if you use colors to separate sections. Colored backgrounds work well with borders to help distinguish headings from data such as subtotals, totals, months, etc. However, you should avoid using color combinations that aren't professional such as a lime green background with a purple font. Backgrounds should be a pale shade, but use common sense when choosing your color patterns. You don't want to cause others to get a headache.
Whether you are determining your earnings or calculating a discount, you can set up Excel to help you determine the percentage discounts. Enter the original price in cell A1, percent discount in cell A2 and discounted price in cell A3. In cells B1 through B3, you will enter the original price of the item and the percentage discounted. In cell B3, enter the formula (B2-A2)/A2 then click the % icon to format the number into a percentage.
Use Keyboard Shortcuts
Shortcuts are essential to using Excel. These days it's not enough to know copy, paste and save; you need to know even more. Here are a few examples of easy keyboard shortcuts:
- Ctrl + 2: Bold
- Ctrl + 3: Italic
- Ctrl + 4: Underline
- Ctrl + 5: Strikethrough
- Shift + Ctrl + F: Font dropdown list
- Ctrl + 9: Hide rows
- Ctrl + 0: Hide columns
- Ctrl + Shift + (: Unhide rows
- Ctrl + Shift +): Unhide columns
- Shift + Space: Select entire row
- Ctrl + Space: Select entire column
One Font to Rule Them All
There is nothing worse than opening up a spreadsheet and seeing a hodge-podge of fonts thrown together. Unless you are developing a file to display all the different fonts Excel has, make sure you stick with one font throughout. This is more a housekeeping item than a hidden secret, but your coworkers will appreciate the consistency of your work.
The "count" and "counta" functions allow you to find the sum of numbers. The count is for numbers and "counta" for everything but numbers, such as names. To use the count function, click on the cell you want your count in, type "=count(" and then select the range of cells you want to capture. Then close the function with a closing ")" and hit Enter. To use the counta function, follow the same rules but replace "count" with "counta."
No Excel? No Problem
If you are working on a computer that doesn't have MS Office and someone sends you an Excel file, you can still view the spreadsheet using Microsoft Excel Viewer, which you can get from Microsoft's website. This is an excellent tool for viewing Excel files and you don't have to purchase anything. You won't be able to edit spreadsheets or create new ones using the viewer, but at least you will be able to see all contents of the file in an easily viewable format.
At TopTenREVIEWS We Do the Research So You Don't Have To.