Hidden Features in MS Excel 2013

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 chartered figures from a website, in Excel, you don’t necessarily have to bother copy and paste 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.

Interestingly, there are many such features present in Excel 2013. First of all, you can definitely pull data from your 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.

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

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.

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.

Get the Sparklines

The usual tabulation and formatting in Spreadsheet is remarkable but it is a 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.

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.

Sum, count, and average without using any mathematical formula

If you want to know some basic things like the 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.

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

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.

Take snapshots

One of the handiest features 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.