The unparalleled data manipulation and stats organizing tool Microsoft Excel is no doubt a time saving app, but just like its sister tools Word and PowerPoint, it too has some hidden features. By knowing which they are, you can get more out of this app.

Import stats from websites

While inserting charted figures from a website, in Excel, you don’t necessarily have to bother copy and pasting them manually. Instead, you could simply import the stats from sites. For doing that, if you are using Excel 2010, go to File, click on Open, and enter the address in the question bar.

import data from SkyDrive and SharePoint.Interestingly, there are many such features present in Excel 2013. First of all, you can definitely pull data from you SkyDrive account, or SharePoint, if required. To do that, go to File| Open| and click on the plus sign which says “Add a Place”. If you want to grab the tabulation from a particular web page, click on the “Data” menu from the ribbon bar, and, from the left, click on From Web. It will then ask you the URL of that page; keep in mind that it might take a minute or two to fetch data from the site. Other than that, you can also import stats from SQL tables, XML files and several other places as depicted in the image posted below.

Import data from web page and several other sources.

Paste special for performing batch calculation

Manipulating data up to a couple of rows and columns isn’t a tedious task, but when you need to perform some math operations on a vast number of cells and rows, it sure isn’t easy. This is where Paste Special comes into the picture.

You can find it at the Paste menu bar, or you can directly get access to it from you keyboard by pressing Alt + Ctrl + V. It helps you insert only the values you have copied and not all the useless codes that come along with it.

Paste Special

Transform rows into columns, and columns into row

Suppose you confused rows with columns, and you realized it only after doing enough damage, is there a comeback from this? Well, apparently there is a way you could transpose the position of cells and correct their position. To do that, select the cells, again, click the small triangle sign on the paste icon, go to Paste Special, and tick the Transpose button.

Transpose rows into columns

Customize the print area

Unlike printing a document, getting hard-copy of spreadsheet files is little thorny. Going with the default settings, you will almost every time get your tables out of the sheet. One smart thing to do is using the Page Break Preview  to check how your tables are going to be printed. If you want to have a say on precisely which cells should get inky, you can drag to include or exclude cells, this way you can include just the cells which you want to get printed.

Print area

Get the Sparklines

The usual tabulation and formatting in Spreadsheet is remarkable but it is little cluttered to grasp if you are running short on time. Try adding Sparklines to your worksheet so that your stats can make sense even at a mere glance. To get the Sparklines, select the cells, go to the Insert menu from the ribbon bar, and select Sparklines.

Sparklines in Excel

Remove duplicates

Redundancy is another common trouble you could bump into while importing stats from sources. Thankfully Excel can help you fix it, just select the table and go to the Data tab, and click on Remove duplicates. A window will pop-up asking the columns and rows you want to check the duplicate entries on.

Remove duplicates

Sum, count and average without using any mathematical formula

If you want to know some basic things like sum of the elements of some cells, its average and count, you don’t necessarily have to use any mathematical formula for that. Excel has this feature where if you only select the cells, it will display the results at the bottom of the Excel window. By right clicking on the status bar you can also enable some other mathematical sorts like minimum, maximum and numerical count.

basic mathematical calculations without using any formula

Watch out the cells

One really annoying thing while working with Spreadsheet documents is as the size of the sheet grows up, it is hard to keep the entire sheet on one screen. Sure you can zoom in and zoom out, but that too tends to elevate the annoyance level. This is where Watch Windows comes into play. This feature of Excel lets you divide your sheet into number of parts. Then you can pick a part and carry on with your work on it.

To get the Watch Window, go to the Formulas tab from the ribbon menu, and click on the Watch Window button. After a resizable window will appear, define the range of cells you want to deal with.

Watch Window

Show all the formulas

Cells, by default, only show you the outcome of the formulas instead of showing the used formula themselves. However, many times you will need to check the formula you used while reviewing the document. To see the formulas working behind the curtains, select the cells, and press Ctrl and minus (-) key. A good thing about all this is, that when you hit that combination, the cells you chose automatically enlarge themselves, which helps you have a better look at such tiny cells containing so many expressions.

Show all formulas in Excel

Take snapshots

One of the handiest feature of Excel is its camera utility. Although veiled from the view, this small utility can help you take snapshots of cells. To use this feature, you will need to add it to the Quick Access Toolbar (See the below image). Click on that small arrow, and select “More Commands”, scroll down the options from “Choose commands from” and set it to all commands. After doing that, locate the Camera option, and double click on it, and select Apply to that window. You will see a Camera icon on top of your screen, select it, and now select the cells you want to take the snapshots of. Those screenshots will get stored in your clipboard and you can either paste that thing on any cell, or create an image of it from any image editing tool.

Enable Camera from Quick Access Toolbar


Also Read:
 
Author

Manish is pursuing a degree in Computer Science and Engineering but spends more time in writing about technology. He has written for a number of Indian and international publications including BetaNews, BGR India, WinBeta, MakeTechEasier, MediaNama, and Digit magazine among others. When not writing, you would find him ranting about the state of digital journalism on Twitter.