MS Excel's Most Useful Tips and Tricks
Excel is a prodigy of spreadsheets which is being used all around the world and in almost every discipline. It can give an extra edge to your competitive skills as it reflects to many aspects of work today whether it relates to Accounting, Finance, Marketing, Project Management, Mathematics & Statistics, Engineering, etc.
In this article I will focus on some of the most important and useful MS Excel tips and tricks that I’ve learned over the years that could categorically help the reader in developing proficiency in using the MS Excel and practicing it in their work routines while saving extraordinary amount of time.
Following is the list of items which will be discussed in this article that relates to the usefulness and importance of MS Excel:
- Auto filling data.
- Moving a cell without copying.
- Adjusting Cell Size - How to adjust the height and width of rows and columns?
- Moving between Spreadsheets of the same workbook.
- Moving between Cells of the same Spreadsheet.
- Copying data that is above or on the left with one stroke.
- Transposing Data.
- Inserting a Table.
- Insert or Delete Rows/Columns.
- Spell-check in MS Excel.
- How to Freeze Panes?
- Merge & Center.
- Hide/Unhide Rows and Columns.
- Sort & Filter.
- Quick and Handy Shortcuts & Formulas.
These are the main areas of our discussion in this article as we discuss more than thirty tips and tricks that could really prove to be useful for the user.
Tips and Tricks
- 1. Auto filling Data.
It is very easy and tremendously useful while entering data without much effort involved. This can be done by using the small square that is visible at the bottom right corner of the selected cell. By double-clicking it and dragging towards any side, (up/down/right/left) you can auto fill the data with incredible ease.
- 2. Moving a cell without copying.
This can be done by using the four point cursor. Drag and move a cell(s) with its data to another cell (or range of cells) by double-clicking on it when the four point cursor appears.
- 3. Adjusting Cell Size - How to adjust the height and width of rows and columns?
Double-click on the boundary of the respective row or column which you want to adjust. Similarly, in order to adjust all the rows/columns in the whole spreadsheet, press (Ctrl + A) keys twice to select all and then double-click on the boundary of any row/column to adjust the cells accordingly.
- 4. Moving between Spreadsheets of the same workbook.
Ctrl + Page Up/Page Down
By using these keys you can shift between the different spreadsheets of the same workbook. This allows you to scroll between all the available sheets quickly.
- 5. Moving between Cells of the same Spreadsheet.
This usually helps a lot during data entry and aren’t too hard to memorize for definitive ease.
- 6. Copying data that is above or on the left with one stroke.
You can copy data on the cell that is above and on the left cell with one hit without using the copy paste keys.
This can also be done by using the little square that is noticeable on the bottom right of the selected cell. By double-clicking it and dragging it towards any side, it will automatically copy the information that is filled in the cell already.
- 7. Transposing Data.
Ctrl + H + V + T
This will allow you to copy the data which is in a horizontal manner into a vertical manner and vice versa.
- 8. Inserting a Table
Inserting a table into the worksheet is quite easy and can be done by selecting the cells range and using Ctrl + T. This will allow you to insert a table comprising of the Rows and Columns selected/highlighted by you.
The table can be designed and edited with ease by using the 'DESIGN' Tab visible under the 'Table Tools' heading. It is to be noted here that the DESIGN tab will only be visible when you click on one or more of the cells in a particular table.
- 9. Insert or Delete Rows/Columns.
After highlighting a Row/Column:
- 10. Spell check in MS Excel
Unlike MS Word, wrong spellings aren’t underlined in MS Excel which roots the misspelled words without bringing them into our notice. In order to spell check a worksheet or even some selected text/document you can press the F7 key and the spell check dialogue box will automatically pop-up on your screen. This can also be done by clicking on the 'Review' tab and then clicking on 'Spelling & Grammar' button.
- 11 How to Freeze Panes?
This is one of the most useful techniques that help a spreadsheet user to navigate between different values of the same heading without memorizing the cell/table headings. This can be done by clicking on the 'Freeze Panes' option which is available under the 'View' tab. What it’ll do is that it’s going to freeze all the cells (Rows/Columns) to the left and above of the selected cell and allow you to scroll through large sets of data while keeping the relevant information in sight.
- 12. Merge & Center.
Merging cells is also a very unique way to remove unwanted cells or to justify text in a way that looks fit to be seen covering all the relevant areas of the entered information. This can be done with the help of the 'Merge & Center' button that is available under the home tab. Select the cells that you want to merge and then click on the 'Merge & Center' button to merge the selected cells. This will allow you to treat all those selected cells as one cell in all.
- 13. Hide/Unhide Rows and Columns.
Once the required information is entered in the spreadsheet and you want to hide certain information for any purpose intended whatsoever, you can hide the respective Row(s) or Column(s) by right-clicking on the respective row(s)/column(s) and then clicking on 'Hide'.
Similarly, if you want to unhide some of the previously hidden information then you can do that simply by right-clicking on the respective row(s)/column(s) and then clicking on 'Unhide'.
- 14. Sort & Filter.
This feature allows you to arrange the data the way you want to by applying the multiple-level sorting for the information that is entered in the worksheet. You can sort the data the way you want while sorting a range or table of data on one or more columns of data.
For example, you can sort by Name, Age, Description, Designation, Department, etc.
- 15. Quick and Handy Shortcuts & Formulas.
There are a lot of useful shortcuts and formulas that come in handy quite often than not. Some of them are listed below:
It is undoubtedly a growing phenomenon that the use and applications of Excel worksheets is increasing day-by-day at a very rapid speed. Every individual has to improvise their skills and continue learning new things to keep them well updated at various levels about the world. I have discussed just a few Excel tips & tricks in this article that could be of great use and importance if used and practiced the right way. These tips & tricks will not only be an efficient tool of understanding but also a great way of saving time and effort. I believe if the reader has learned any (or all) of these tips and practiced them eventually after reading them, then it is an effort worthwhile and a task I consider accomplished as educating is one of the best things, we as humans can do. There are a lot of material helps available on the internet that tailors all the things you and I need to know about MS Excel or even the MS Office Enterprise. This article is an effort that has been collated through a number of years of use and experience. Hope you all like this article as much as I have enjoyed writing it as this is a pure work of the knowledge that I have on the topic. I invite all readers to comment below staying relevant to this article. Thanks for reading!