9/15/2015 4:53 AM
Microsoft Excel is one of the most powerful Office apps and we want you to gradually maximize its functionalities. Some are intimidated by the endless rows and columns while the others simply just lack knowledge on what they can do to improve their spreadsheets or manage the data that they have.
Here’s our top 5 Excel habits that we know will be useful to you.
1 – Conditional Formatting
Did you know that you can tell Excel to highlight the top 10 items in a list or make a cell stand out if it’s greater than or less than a certain value? You can even set your own specific formatting so that every time you type a value, for example “5”, Excel automatically turns the font color to red.
This is a handy tool if you are entering a lot of data such as scores and you want to view outliers at a glance. The Conditional Formatting option is available under the Home Tab.
2 – Get Rid of the #VALUE! Or #DIV/0! Error Message
Isn’t it annoying to see Excel’s error message just because you haven’t entered the data needed for cells with pre-encoded formula? If you have encountered these error values, then you’d be glad to know that there is a way to get rid of them.
Introducing the “IFERROR” function. When you use this function, you can replace the error message with something more appropriate like the number 0, a dash “-“ or some text value indicating that a data is still missing.
3 – Finding Data quickly using CTRL+F
If you have a huge database file and you want to find something specific or you want to know how many of that specific item is contained in the database, you can simply hit CTRL+F. Type the item you want to find and select Find All. At the bottom, you’ll see the number of items present. Clicking on the results or clicking “Find Next” will bring you to the cell containing the search query.
4 – Replace blank cells with a fixed value
In some of your excel files, there are times that some cells will be left blank because there’s no data on it. What if you want to replace these blank cells with a value such as 0 or a “-“? You can use the “Go To Special” option.
In the Home tab, look for “Find and Select” and click “Go to Special”.
This will open a window where you can select BLANKS. When you click OK, all the blank cells will be highlighted.
Notice the blank cell that’s not highlighted but selected. This is where you can input any value you want to be copied to all the other cells. Press CTRL+ENTER and all the other blank cells will be populated instantly.
5 – Fix a formula on a cell using the $ symbol
Computing rows and columns of data in Excel is easy. Simply enter the formula in the first cell, copy-paste it to the others and Excel readily identifies which data should be part of the calculation. But what if you want a part of the formula to be fixed in a particular cell? For example, you are calculating for the percentage of a certain item vs. a total sum. The summation is a fixed cell in the formula. How do you tell Excel that a certain cell in the formula should be fixed? Use the $ symbol.
Here’s an example.
To get the % from total of each Sales Revenue, you need to divide the sales revenue by the total revenue found in B8.
Copy-Paste would result in an error because Excel automatically adjusts the formula and gets the value of the cell below the one which contains the total. It does this because it simply follows the pattern of the first formula.
To remedy, add a $ symbol before the column (B) and the row (B) before you copy-paste the formula to the other cells. On the picture above, notice the formula in cell C4. This will ensure that the formula always gets the value from a fixed cell, in this case cell B8.
Preview Photo by:
photo credit: <a href="http://www.flickr.com/photos/46411239@N05/5394686089">Excel_2010 box</a> via <a href="http://photopin.com">photopin</a> <a href="https://creativecommons.org/licenses/by/2.0/">(license)</a>