5 Powerful and Most Useful Excel Features you need to know

Microsoft Excel is the most useful and easy tool for business analysts. It has large number of useful formulas, features and bundles of interactive charts. But, most of us are not known of all of them and there are some more features which are powerful and easy to use to make our work simpler. You might not have noticed some of the useful Excel 2013 features like Sparklines, Slicers, Conditional Formatting and other formulas which add value to your work. In this article, I will take you through them and will give you an idea on what are those and how to use them.

Most Useful Excel Features

Among many Excel features, there are some hidden features which are easy to use and you many not know all of them. Without any further delay, we will look at 5 such Excel features.

Sparklines

Sparklines were first introduced in Excel 2010 and are used to represent visualizations for the trend across the data in a row. It fits in a single Excel cell and saves the space on the worksheet. This is a cool feature and is very easy to use. Calculating the trend for row data and placing the visualization in the single excel is really a great feature to use.

In order to create your own Sparklines, select the range of data. Click insert on the ribbon and select the type ofSparklines (Line, Column or Win/Loss). Next, enter the range of the target where you want to show the Sparklines. For more information on how to create Sparklines, visit Office Blogs.

Conditional Formatting

Conditional Formatting is a well known feature of Excel. It is used to visually present the data based on the conditions met. It is also useful to create heat maps. This would be helpful to find the interesting patterns by exploring the data effectively.

To create the heat map, select the data and head over to the ribbon. Under Home, click Conditional Formatting and then click Color Scales. Now, pick the color scale. You can even set the color scale by editing the formatting rule. For more information on Conditional Formatting, visit Office Support.

SMALL and LARGE Functions

We all know about MAX and MIN functions. They give you the maximum and minimum values of the selected data respectively. But, in order to find the 1st, 2nd, 3rd or nth largest or smallest value of the selected range if data, we can make use of LARGE and SMALL functions respectively.

In this example, in order to find the top two products for each month, we made use of MATCH and INDEX functions along with LARGE and SMALL functions. For more information, visit SMALL and LARGE functions.

Remove Duplicates

Do not blame me for mentioning this feature in this list. It is very important to get rid of redundant data from the available huge amount of data. It is one of the best ways for cleaning and organizing the data and so thought of having it in this list of powerful Excel features. Removing Duplicates feature was introduced from Excel 2007 and is helpful to remove duplicates which is the most important problem which we face.

To remove duplicates, select the data and head over to the ribbon. Under Data, click the Remove Duplicates button and what you see the data without duplicates. For more information on how to Find and Remove Duplicates, visit Office.com.

Slicers

Slicers act as visual filters. It helps you to visualize the subset of data as a connected chart or as a raw data. For example, if you want to show the trend of sales of various products, then you can create the interactive sales trend chart using Slicers. Based on the product you select, respective chart is shown. Slicers were first introduced in Excel 2010 and enhanced a lot in Excel 2013.

In Excel 2013, if you want to add Slicer to your charts, select the data range and click on insert > Slicer. Now, select the part of the data you want to use as a filter. In the image above, Product column is used as a filter. How here for more information on how to use Slicers.

For more details on these features and to download the example workbook, visit Office Blogs

Read More

How to get a list of files in a folder into Excel

In this post we will see how to get a list of files in a folder into Excel. We will show you how to use Excel to view Files and Folders details in Windows, by importing all files and folders details in to Microsoft Excel to keep track of file size, file type and last modified date.

We have many files and folders in our Windows PC and we may delete or add more files and folders frequently. There could be some folders and files which are very important to us and someone might edit them without our notice. But, we cannot keep on looking at each and every folder and file to know which one was edited. If it was modification of a file or folder, we can sort them by using ‘Last modified’ and we can get to know which were modified recently.

But, what if some of the files and folders were deleted from that directory? We would not be in a position to keep track of what were exactly deleted. But, you can use Excel to view files and folder details of directory which would be helpful to you to know at least the list of files and folders that were deleted.

Import and get a list of filenames into an Excel sheet

If you use Microsoft Excel to import all details of files and folders in to Excel, it will help you keep track of last modified date and time, file types, list of files, files size and many more regularly. To do this, follow these simple steps.

Move to the directory or folder on Windows File Explorer which you want to keep track of. Here, I want to keep track of my files and folders of Documents folder. Copy the path of that directory.

Now, open any web browser of your choice and paste the copied path (Path of the folder you just copied in the previous step) in the address bar of the browser. I used Google Chrome here. Just prefix the URL by file:///and the content will be shown as the web page.

To save this web page as the offline copy, press CTRL+S or right-click on the web page and select “Save Page As”. Select the destination, give it a name and save the webpage.

Now, via Windows Explorer go the location you saved the offline webpage and copy the path. The, open the Excel sheet and tap on the Data tab and click on From Web. It opens up the Window and in the Address bar paste the copied path and click “Go” button. It will load all the contents of the webpage.

It shows you the yellow boxes with arrows and it will allows you to select the required frame. You can find here that I selected the part I want.

Once done with these steps, now click on import button and you see that all the files and folders details are imported to your Excel sheet in no time. You can see that, data is shown the columns and gives us the clear idea of every detail.

Conclusion

As we are engaged most of the time, in organizing files and folders, it is advisable to import files and folders details in to Excel. This would be helpful if any of your file or folder was to get deleted. The data in the Excel sheet does not refresh, so we need to import the data regularly. You can use this method, when to not only keep track of file changes, but also filenames.

Read More

Record a Macro to create Shortcut Button for Multiple Formats in Word and Excel 2013

By default, the Quick Access Toolbar of Microsoft Word 2013 and Microsoft Excel 2013 contains only three options – Save, Undo and Redo. But, what if I say that this is possible to add more custom buttons? On the other hand, we often apply different formats such as Bold, Italic, Underline, Heading 1, Heading 2, etc, to our writings. Sometime, we need to apply more than one formats (bold, italic and underline or bold and heading 1, etc.) multiple times. Suppose, you need to do so 50 times. This would mean, to complete styling for one time, you need to click on three buttons or so.

To get rid of this time consuming work, you can record a macro and create a shortcut button to implement multipleformats at once. Let us see how to do this.

Record a Macro – Create button to add multiple formats

This is very easy and not very time consuming. You can do this in Word 2013 as well as Excel 2013. The following steps are done with Word 2013, but Excel users can also follow these steps since they are same.

First, open your Word 2013 app and go to View tab. After that, click on Macros and select Record Macro.

Then, you will get a popup as follows,

You need to enter a name and a description, so that you can identify it quickly. Also, make sure that All Documents (Normal.dotm) is selected. After entering all these detsila, hit the OK button. Thereafter, your cursor would look like this –

Now, you can select any format. Ex.: Bold, Underline etc.

After selecting or clicking on all those formats, hit the Stop button that is positioned in the bottom of Microsoft Word.

To pin this recorded Macro to your Quick Access Toolbar, navigate to File > Options > Quick Access Toolbar. Now, expand the drop-down menu and select Macros.

You will get your Macro in the left side. Just select it and click on Add button.

If you want to give it an icon, just select it from right side and click on Modify button.

After that, you will get icons to add.

You will now get a new icon on your Quick Toolbar.

Whenever, you wish to apply those formats, just select the text and hit that button.

Instead of wasting your time to apply various formats or style to to text, you can simply record a macro, add it to your Quick Access Toolbar and utilize it in accordance with your requirements.

Read More