ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

MS Excel: Most Useful Tips and Tricks

Updated on December 2, 2017
TheMAS profile image

The MAS has a MS degree in Information Technology Management and loves to write about technology and software related aspects.

MS Excel worksheet
MS Excel worksheet

INTRODUCTION

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, education and training 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 which could categorically help the reader in developing proficiency in using the Microsoft excel spreadsheets and practicing it in their work routines while saving extraordinary amount of time. Scroll down for a little excel help as well as some relevant excel training without attending any Microsoft excel courses or excel classes that require payments.

Quick Overview

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. 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.

MS Excel: Cell square. Visible on the bottom right of the selected cell.
MS Excel: Cell square. Visible on the bottom right of the selected cell.
  1. 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.

MS Excel four-point cursor
MS Excel four-point cursor
  1. 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.

  1. 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.

  1. 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.

Table 1.5: "MS Excel's Most Useful Tips & Tricks".
Table 1.5: "MS Excel's Most Useful Tips & Tricks".
  1. 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.

Table 1.6: "MS Excel's Most Useful Tips & Tricks".
Table 1.6: "MS Excel's Most Useful Tips & Tricks".

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.

  1. 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.

  1. 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.

  1. 9. Insert or Delete Rows/Columns.

After highlighting a Row/Column:

Table 1.9: "MS Excel's Most Useful Tips & Tricks".
Table 1.9: "MS Excel's Most Useful Tips & Tricks".
  1. 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.

  1. 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.

MS Excel: Freeze Panes Option.
MS Excel: Freeze Panes Option.
  1. 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.

MS Excel: Merge & Center button.
MS Excel: Merge & Center button.


  1. 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'.

  1. 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.

MS Excel: Sort & Filter option.
MS Excel: Sort & Filter option.
  1. 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:

Table 1.15: "MS Excel's Most Useful Tips & Tricks".
Table 1.15: "MS Excel's Most Useful Tips & Tricks".

CONCLUSION

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!

POLL

4.9 out of 5 stars from 20 ratings of How much is this article useful?

Which version of MS Excel do you prefer to use?

See results

© 2017 Muhammad Ali

Post a Comment

Submit a Comment

No comments yet.

working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)