Ads 468x60px

Thursday, May 11, 2017

5 Helpful Tips to Using Excel for Working with Huge Datasets

Unbeknownst to some, one can possibly do a surprising amount of analysis on huge data sets with Microsoft Excel without using other complex software. Microsoft Excel is such a powerful tool; nobody has ever learned everything about it. There are always new things you can discover.

Most statistical professionals and data analysts are often confronted with huge, unclear, or complex data, which becomes nearly impossible to analyze. One example of this is if a certain business has a spreadsheet containing millions of transactions, consuming large storage space. This issue can be very challenging to a business as it is important to clarify data and come up with a meaningful result.

Here are five helpful tips for using Excel for working with huge datasets that will surely make your life easier.

1. Hide Unused Columns, Rows, or Regions but the Working Area
It is extremely helpful if you can focus on just the work area to keep yourself from getting distracted by unused areas. You can usually hide a column or row to protect formulas and data as well. In order to hide an entire row in a sheet, just select the row header and then choose Row from the Format Menu and select Hide. Repeat the same process to hide unused columns. Simply select the column header in the first empty column, choose Column from the Format menu and then select Hide.

2. Use the Excel Index Function
Many use VLOOKUP in Excel but have never used the more improved alternative which is the index match. Excel index function is useful when you want to fetch the value of a data point with the specific row and column number of the data. You can use this powerful function to retrieve specific values or even entire rows and columns.

3. Create Custom Lists
There are times when sets of data seem to repeat themselves throughout a project, which means same values are entered in several spots. When this happens, consider creating a custom list. To do this, go to the Tools menu and select Options. Then, click the Custom Lists tab. Enter each item from your list into the List Entries control. Make sure that you enter one entry per line, in the right order in which you want it to appear. Once completed, click Add. The list will be copied to the Custom Lists control. Then, click OK to close the Options dialog.

4. Use Filters
In order to easily summarize the huge amounts of data in your Excel sheet, one powerful function that you can utilize is the use of Filters. You can create filters on columns that can hide rows that do not match your specific filter.

5. View Formulas Quickly
If you prefer to display formulas instead of the evaluated results in a sheet, you can actually do so by choosing Options from the Tools menu and selecting Formulas on the View tab. For a much quicker way, press Ctrl + ~. This way you can toggle between formulas and normal view.
*Read my Disclosure


Post a Comment