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

How to create Custom Excel Functions

Microsoft Excel Pack comes with the many pre defined functions which does the maximum job for us. We never need any more functions other than those built-in functions in most of the cases. But, what if you are in need of some functionality which was not being provided by any pre defined Excel function?

Microsoft Excel allows us to create Custom Excel Functions or User Defined Functions using VBA. We can create Custom Excel Functions with the functionality we want and they can be accessed in the Excel Sheet as regular Excel Functions using “=” followed by the function name. I will take you through the steps of creating custom Excel Functions using VBA.

Create Custom Excel Functions

Since we will be creating the Custom Excel Function using VBA, we need to enable the “Developer” tab first. By default, it is not enabled and we can enable it. Open the Excel Sheet and click on the Excel button and then click on “Excel Options”. Then check the box, beside “Show Developer tab in the Ribbon”.

Now, to open the Visual Basic Editor, tap on the Developer tab and click on “Visual Basic” icon to launch the Visual Basic Editor.

You can even use the keyboard shortcut “Alt + F11” to launch the Visual Basic Editor. If you use this keyboard shortcut, then there is no need to enabling the Developer tab also.

Now, everything is set to create the Custom Excel Function. Right click on the “Microsoft Excel Objects”, click on “Insert” and then click on “Module”.

It opens the plain window which is the place to write code.

Before, writing the code, you need to understand the sample syntax which needs to be followed to create Custom Excel Function and here how it is,

Function myFunction (arguments) return type

myFunction = some_calculation

End Function

There is no ‘Return’ statement as we have with normal programming languages.

Insert your code in the plain window which just opened. For example, I will create a function “FeesCalculate” which calculates the ‘8%’ of the value provided to the function. I used the return type as “Double” as the value might be in decimals also. You can see that, my code follows the syntax of VBA.

Now, it is the time to save the Excel workbook. Save it with the extension of ‘.xslm’ to use excel sheet with Macro. If you do not save it with this extension, it throws an error.

That’s it!

Now, you can use the User Defined Function in the Excel sheet as normal Excel function using “=”. When you start typing “=” in the cell, it shows you the created function along with other built-in function.

You can see the example below:

Excel Custom Functions cannot change the environment of Microsoft Excel and thus they have limitations.

Limitations of Custom Excel Functions

Custom Excel Functions cannot do the following,

• Insert, format or delete cells on the spreadsheet.
• Changing then value of another cell.
• Adding names to the workbook.
• Rename, delete, move or add sheets to the workbook.

There are many more such limitations and mentioned are some of them.

These are the simple steps to be followed to create Custom Excel Functions.

Read More

Edit PDF Documents using Word Online, free

We normally convert Word documents in to PDF files and when we want to edit these PDF files, we just make a copy of the source Word document, make necessary changes and then export the edited document as PDF once again. We have seen how to edit PDF files in Word 2013, now let us see how you can use Word Online to edit PDF documents, free, without worrying about the location and formatting of the source document.

Use Word Online to edit PDF Documents

You can use Word Online to edit PDF documents in the browser itself. It may not perfectly suit for PDF documents with a lot of charts or graphs, but if the PDF documents have simple text, then you can use Word Online to edit them. Word Online is the best option to edit PDF files with a less number of images and simple text. You will be using Word Online to edit PDF files by visiting OneDrive. Here are the steps to be followed.

Visit OneDrive.com and sign-in with your Microsoft Account. Upload PDF documents which you want to edit in to it. Once the upload of the PDF documents is done, double-click on the uploaded file. This is the PDF documents which is not edited yet and we are going to do that now.

Click on the “Edit in Word” option which is on the top. It asks for your confirmation, to convert the PDF documents to Word to make it editable and click on “Convert”. It makes a copy of your source PDF file and it is not altered at all.

If you want to view the layout of your PDF file, click on “View”. As, we want to edit the file, click on “Edit” button. Now, the file gets opened in Word Online and can be edited.

Now, it is like you are editing a Word document. You can edit it easily by adding or removing tables, adding formatting, adding styles to the text, add or remove images and more.

Once you are done with your editing, then click on the “File” menu and select “Save As” and choose “Download as PDF”. This downloads the edited PDF document to your Windows computer.

Using Word Online to edit PDF files is easy and preferable, as you do not need any software to be installed in yourcomputer. You can get rid of PDF editors and other third party plug-ins.

You can use Google Drive to extract text from images or PDF files. I tried both of them and I see Word Online is doing a better job of editing the PDF file.

Read More

How to hide Most Recently Used Fonts list in Word 2013

As far as Microsoft Office components are concerned, they contains most general settings that a user may need. This means that, not all features may be important for a single user. Consequently, if you don’t like a feature, that doesn’t means its useless, some other user may find it useful. So in such case, users may opt to disable that feature. Today we will see how you can disable or hide the Most recently used fonts or MRU list in Word 2013.

For example, the Most Recently Used (MRU) list of fonts displayed in the drop-down menu, appears as shown above. This is shown when you just select text in your Word while editing.

Hide Most Recently Used Fonts list in Word 2013

Making mistakes while editing the Windows Registry could affect your system adversely. So be careful while editing registry entries and create a System Restore point before proceeding.

1. Press Windows Key + R combination, type put regedit in Run dialog box  and hit Enter to open the Registry Editor.

2. In the left pane of Registry Editor, navigate here:

HKEY_CURRENT_USER\Software\Policies\Microsoft

3. In the left pane of above mentioned location, right click Microsoft key and select New > Key. Name the newly created key as Office. Similarly create sub-key 15.0 to Office key. Again create sub-key to 15.0 and name it Word. Finally, create sub-key to Excel and name it as Options.

4. Highlight Options and come to its right-pane such that registry location becomes:

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\15.0\Word\Options

Right click in the blank space, select New -> DWORD Value and name the registry DWORD as nofontmrulist. Doubleclick this newly created DWORD and give it a value 1 to disable MRU list in font dropdown. Click OK.

Close Registry Editor and reboot to make changes effective. After restarting the system, you’ll find that MRU list font dropdown is hidden already.

Read More