Spreadsheet programs come and go. One that has stood the test of time and remains an invaluable tool for any business owner is Microsoft Excel.
Introduced in 1987, MS Excel has grown in popularity over the years especially with the release of Version5 in 1993. Version5 was compatible with Windows which made the software easier to use.
Likewise, Version5 provided the user with faster calculations, more advanced features for creating graphs, pivot tables, and the use of a programming language called Visual Basic for Applications.
Today, many regard MS Excel as the gold standard among spreadsheet programs. The fact that MS Excel accounts for 85% of the office market further strengthens this argument.
For many business owners, the use of a spreadsheet program offers many benefits:
- Organize and store data
- Tools to analyze data
- Prepare charts and graphs for presentation
- Preset formulas for quick and easy calculations
- Ability to synchronize with other software programs
And MS Excel makes all of these – and more – easier to do! In business, getting organized, tracking your expenses, and analyzing performance are keys to staying profitable.
MS Excel is an indispensable tool that can be literally accessed at the palm of your hand.
Top 10 Microsoft Excel Tricks That Will Help Any Business Owner
Learning its basic functions will go a long way in helping you manage your business. However, here are 10 Microsoft Excel tricks that you should learn so you can take your business to the next level.
1. Conditional Formatting
Storing data in MS Excel is an easy way to organize information that is key to the success of your business. However, as data begins to accumulate, it may be difficult to make sense of all of those numbers.
Conditional Formatting is a feature in Excel that helps you set the parameters of your data. This Excel trick will make it easier for you to find what you are looking for.
Let us assume you are a foreign currency trader who wants to keep track of currencies that are depreciating by 5% every week.
Simply click on “Conditional Formatting” highlight the column where the relevant data has been entered and set the parameter at “less than 5%”. Once you scroll down the data, all the currencies that conform to your search parameter of “less than 5%” will be highlighted in color.
By learning how to use Conditional Formatting, it will be easier for you to evaluate all of the data that you have been collecting.
2. Removing Duplicate Data
When you are constantly uploading massive amounts of data, duplication is always a very real possibility. We will share with you our experience during the content creation process.
Optimization is an important part of content writing. If the blogs are not optimized, it will not be found on the Internet. One of the most important processes in content optimization is keyword research.
A particular topic can yield thousands of keywords. Most of these are duplicates and if the content writer does not clean out the keyword spreadsheet, he may be misled by the discrepancies in search volume between the same keywords.
In order to remove the duplicates, all you have to do is go to “Data”, highlight the columns where there are duplicate data, then click “Remove Duplicates”.
Once the spreadsheet has been cleaned of duplicates, it will be easier to work through the data.
3. Recover Lost Files
For sure this has happened to you before.
You have been uploading and analyzing data for the past hour. Then, without warning, the power goes out. Fortunately, MS Excel has an Auto-Save feature.
Unfortunately, your current MS Excel Auto-Save feature is set at every 20 minutes. You have lost data that you have been uploading the last 20 minutes! Once power is restored, you will have to re-upload all of the data that has been lost.
We will teach you an important MS Excel trick that will help you relax and breath a sigh of relief whenever there is a power outage.
First, go to the “File” menu. Go to “Info”, then click on “Manage Versions”. You will see 2 options. Click on “Recover Unsaved Workbooks”.
This simple tip will help you save productive minutes. When you have power back on, all you have to do is to start where you left off.
4. Paste Special
One of the most common activities you do in MS Excel is to copy and paste. However, you may want to view the data in a different way. For example, you may want to have the data presented in a percentage format.
Paste Special will enable you to change the look of your data with a few easy clicks.
Going back to our example, let us assume you want a column of data converted into percentage format. Highlight the data that you want to convert, click copy, then paste it on another file.
Here is the step-by-step process on how to convert the numbers into percentages:
- On a separate column, write down “100” since, in order to convert numbers into percentages, these numbers have to be divided by 100.
- Copy the value of “100”.
- Highlight the entire column that you want to be converted into percentages.
- Right click, then choose “Paste Special”.
- Under “Operation”, choose “Divide”.
- All of the numbers in the highlighted column will be automatically divided by 100.
- Click the “%” icon to insert the percentage symbol in all the numbers in the column.
5. Freeze Panes
After years spent building a solid reputation in the real estate industry, you are finally invited to present your services to one of the state’s biggest and most successful property development companies.
This is an opportunity you cannot pass up! The Vice-President of Business Development wants to know how you target potential buyers for the properties in your list.
You present to the VP your Buyer’s Profile spreadsheet which is a result of years of hard work and consistent data collection.
However, by the time you get to column AA and below row 50, the VP cannot see the header cells. It is hard to appreciate the effectiveness of your system if the main decision maker will not be able to follow its movement.
The solution is to freeze panes to keep the headers in place while you continue to scroll through the data.
All you have to do is to click to the cell below the header you want to freeze. You can also click to the left of the column you want to be frozen. Go to the “View” tab and choose “Freeze Panes”.
6. Extend Formula Across/Down
Before MS Excel, you had to be familiar with the M+ key on your calculator. This is the key which allows you to store a number that you can use to multiply or divide another value.
With MS Excel, all you need to do is to write down the formula on a cell and copy it across or down in order to repeat the same calculation.
Clicking “Copy” then dragging the formula down or across highlighted cells would be one way of extending the calculation. However, if you have a large amount of data to calculate, simply double click the formula on the cell where you want it to run.
7. Filters
Filters allow you to explore data faster by hiding information that is not relevant to your search.
For example, the value “Fruits and Vegetables” are no longer relevant to your data analysis. MS Excel can filter this value out whenever the program comes across it.
The newer versions of Excel allow you to run filters on numerical values such as percentages or parameters as “is greater than”. Likewise, the current versions of Excel can also filter out cell colors.
8. Print Optimization
Perhaps one of the knocks on MS Excel is that having documents printed out is not always easy. For sure, you know what we mean! Sometimes you do not get exactly what you wanted to print out. It may take a few tries before you finally get the copy that you want.
The key to optimizing MS Excel’s printing capabilities is to follow a set of steps:
- Print Preview
- Fit to One Page
- Adjust Margins
- Print Selection
- Printing Headers
- Choose Portrait vs. Landscape
- Spreadsheet Design
It will take some practice to get the hang of printing. However, this will be a task that you should expect to perform several times during the course of your business.
9. Flash Fill
Flash Fill is a feature that was added to the 2013 version of MS Excel. It is a wonderful tool to use for cleaning up and organizing data.
Some users have remarked that the Flash Fill feature is MS Excel thinking on its own. It anticipates your intended action and by simply clicking on the “Flash Fill” button, MS Excel will execute the task.
For example, you have a column of customers’ names that are presented as follows:
Last Name, First Name
Let us say you want to extract only the first names of the customers and copy them on the column beside it.
All you have to do is to write the first name of the first entry on the cell where you want to print the data. Next, go to “Fill” and click on “Flash Fill”.
Just like magic, MS Excel will copy all of the first names on the list to the column and run the data in order of their appearance.
10. Index-Match
Users of MS Excel who have used its VLOOKUP function to sort out data will share a common opinion – INDEX-MATCH is a better feature!
With VLOOKUP, the data will get affected whenever changes in column positions are made. This will not happen with INDEX-MATCH.
INDEX-MATCH is a great feature to use if you want to sort out specific data from your spreadsheet. Simply choose the location where you want the sorted information to appear. Then, set the parameters or conditions of your search in the INDEX-MATCH formula.
Within seconds, MS Excel will retrieve the data you want and present them in the location you chose.
Conclusion
There are other spreadsheet programs that you can consider for your business. For example, Google Sheets is also becoming popular with entrepreneurs. Other noteworthy spreadsheet programs include Numbers by Apple, Open Office Calc by Apache, and Zoho Sheet by Zoho.
However, MS Excel remains the gold standard to which these software programs are measured against and with many good reasons. MS Excel is easy to use, highly-navigable and includes key features that are very important to running a business.
Like all skills, becoming proficient in MS Excel will take time and practice. Working with MS Excel will be time well spent. Do not be intimidated by the commands and features. Keep practicing until MS Excel becomes second nature.
Did you find this article helpful? If so, please feel free to share it with people who may want to learn more about MS Excel.
And if you have other MS Excel tips and tricks that you wish to share with our readers, please feel free to write them down in our comments section.
