If you are a Daily Excel User this post is for you. I have great passion for learning Microsoft Excel and I tried to explore all possibilities it provides. I am not going to say that I know all the features Excel provides. From my point of view learn Excel in a limited way to suit our need and go deep into the features we learned.
If you go deep in to using Microsoft Excel, you will realize one thing that you are nowhere near to the possibilities Excel offers. Excel has lots of powerful features that can ease your job. Are you one among the bored daily Excel user? Listen! Excel can make your job hours entertaining but all depends how you use this wonderful office application.
For novice Spreadsheet users, I am sharing 10 killer features I found in Excel. Try this in your everyday Excel Spreadsheet use and you know how it is going to change your working hours.
Awesome 10 Spreadsheet Features in Excel for Daily Excel User
1. Search Entire Work Sheet form any Sheet:
If there are multiple worksheets in your workbook you must use this search feature to a find specific content. Press Ctrl+F and go to options and change “Sheet” to “Workbook” against “Within”.
2. Must to Use Shortcut Keys in Excel:
To speed up your job you must use keyboard shortcuts in Excel. There are must to use Excel shortcuts other than the cut, copy and paste ones.
- Ctrl+D copy the content from cell above
- Ctrl+R copy content from cell left
- Ctrl+~ shows formula
- Ctrl++ insert new rows above the selected row
- Ctrl+- delete the selected row
3. Wildcard Characters in Search:
Being a daily Excel Spreadsheet user, you must know the usage of Wildcard Characters in Excel.
? [question mark]
This wild card character can be used when you are not sure about a spelling of a word. For example when you want to find a name Prashanth and you are not sure about the spelling, you can use Pr?shanth. It will find the name “Prashanth” as well as “Preshanth”.
“*India” can be used to find any words like South India, North India etc. But most cases it’s not necessary unless you enabled “Match Case” in Find and Replace (Ctrl+F) advanced options. But it will be come in handy to find misspelled words in Excel. You can find the word “colour” as well as “color” bu using asterisk wildcard character in Excel. Use “col*r”.
Rarely you may want to find a “*”, “?” or a “~” itself on your worksheet. Since all these are wildcard characters you can’t find them with the normal find. To find a “*” you have to put a “~” before it.
~? finds question marks
~* finds asterisk
~~ finds tilde
But do remember that the above are the main wildcard characters in Excel. There are few more advanced wildcard characters in Excel, that may not come necessary. So I’m not mentioning the same here.
4. Subtotal Function:
Everybody uses “sum()” function in Excel in the place of subtotal also. In a database with subtotal and grand total, commonly people uses “sum()” in the first place and for grand total they simply add all the subtotals using the “+”. But things will be pretty easy and error free if you use subtotal function. Any doubt? carefully check the below screenshot that will let you know the difference between subtotal and sum function in Excel.
SUBTOTAL(function_num, ref1, ref2, …)
You can put 9 in the place of function number and put a comma and select range to sum. It’s simple if you see the screenshot below.
5. Sumif – The Leader of All Excel Functions:
I explored “Sumif()” function in all its possible ways and honestly I liked the way it produce results. You can sum a cell or range of cells based on some criteria. One way I used it is to find the balance qty. of different items after every use. I am not going details here. Regular use will make you a master “sumif()”.
If you go to learn through examples it consume your time. So here also we can approach the screenshot method to know about Sumif function.
In the example below, you can see Stock on one side and the Usage on the other side. Though sumif function put on the stock side, it actually applied in the usage side. It produces the summary of usage. So we can find the balance qty. easily. Over the time your usage data may get increased. Accordingly change the data range in the sumif function. Enter the below data in an Excel sheet as it is to experiment with it.
6. Protect Sheet to Avoid Accidental Keystrokes:
If you have a very important data, lock the sheet before moving around. Some accidental keystroke can harm your out put. Use the keyboard short cut Alt+T+P+P. You have to press and hold the “Alt” key and then press the keys T, P, P one by one. Then put a password. Save the file then. Similarly you can remove the password too. Your data is accessible to others but can’t modify it.
7. Check your Formula with Auditing Tool:
Anybody use Auditing Tool in Excel. If you are a daily Excel user, chances are that you have to deal with lots of Formulas.
Check your formula with the awesome auditing tool that Excel provides. Go to the cell where your formula is and use shortcut key Alt+T+U+T. Press and hold “Alt” key and press T, U, T one by one. It’s above explanation!
8. Name Cells:
You can name cells with customized names other than the usual cell address like K12, G9:G12 etc. Select the individual cell or rage of cells and right click. Select “Name Range” and type the name you want. See the screenshot below to know how naming cells are useful in Excel.
I have named the range of cells C9:C12 as “Earning”. So I can directly used the same in formulas.
9. Find Number of Days Between Two Dates:
If you want to find ageing of bills, a feature you can see on Tally, there is a simple solution on Excel. Just subtract the dates from the current date. The trick is like this.
In any cell use the formula =today()-cell address. You will definitely get another date. Here use shortcut key Ctrl+1 to got to format cells. Go to Number tab and select Number. See the date change to numbers and you can find the ageing of your bill in Excel. A feature I used in Excel often.
10. Paste Special:
As a daily Excel user, I frequently used this option in Excel. If you are not using paste special in Excel, you are missing some awesome features of Excel. Use this to transform data entered horizontally to vertically (transpose), copy formula result and paste as mere values, copy column widths etc. You will be surprised to see the possibilities Paste Special offers. Obviously, first you need to copy something to use Paste Special. Then use the shortcut Alt+E.
You can dynamically use lots of Excel features and the best examples are Find and Replace and the Paste Special above. You may be regularly using it. But may not notice the hidden features in it. Similarly you can explore the possibilities of above “Sumif()” also. Excel offers everything to fulfil your data entry quench. But how you are using it depends the output.
Any query using any of the above? Please post the same in below comments.
Image Credit : Sarah G