Microsoft_Excel_2013_logo.svg

How to Find Duplicates in Excel (And What to Do With Them)

Being able to find duplicates in Excel is a very valuable skill to learn, as it enables you to quickly clean up data and troubleshoot problems. Sometimes you just need to locate the duplicates, other times you need to find and remove them. Regardless of your end-goal, this tutorial will teach you two different ways to find duplicates in Excel and how to quickly remove them (if you so desire).

1. Remove Duplicates Button

The most obvious way to find and remove duplicate values in Excel is to simply use the Remove Duplicates button (in the Data Tools group on the Data Tab). The functionality is actually pretty flexible, allowing you to select either a single column or a whole series of columns and then removing duplicates based on the criteria that you input. So, you can either remove duplicates from a single column (while leaving the rest of the table intact) or delete an entire row if it contains a duplicate value in one or more columns that you specify. So, if you just need to quickly clean up your data and completely remove any duplicate values the dataset, the Remove Duplicates button is the way to go.

2. Conditional Formatting

If you simply want to find duplicates in Excel (without necessarily deleting them), the best way to accomplish that is with Conditional Formatting. Simply use the Highlight Duplicate Values option. Once you’ve set the formatting, you can then apply a filter to the column with duplicate values and filter down to the duplicates. At that point, you can either edit, delete, or just ignore the duplicate values. The best part about using the Conditional Formatting method to find duplicates in Excel is that it’s dynamic! So, if you deciede to delete a few of your duplicates (or add in some more duplicates later), the Conditional Formatting will automatically update. Simply refresh the Filter to see the most current duplicates.

Learn to Find Duplicates in Excel and Much More!

Sign up now for our free email newsletter: Not only will you receive Excel tips and tricks directly in your inbox, you’ll also receive a free Gantt Chart template, which you can use to plan your next project.

Microsoft_Excel_2013_logo.svg

4 Ways to Use Conditional Formatting to Power Your Analysis

Conditional formatting is a powerful way to incorporate formatting into your data analysis. When you use conditional formatting in Excel, you make your formatting dynamic, which enables your formatting to complement and add to your data analysis. If you use the four tips described in the video, you can quickly and easily enhance your data analysis and make it more useful for your end-users.

1. Pick Your Favorites With Highlight Cells

The Highlight Cells conditional formatting option allows you to easily identify subsets of data within your data set. If you want to quickly find all of the values in your data that are greater than 10, simply use Highlight Cells and use conditional formatting to make all cells with a value greater than 10 red. Highlight Cells also offers several text options, so you can use this function with either text strings or numbers.

2. Find the Outliers With Top/Bottom Rules

If you just need to identify the outliers in your data, simply pick the Top/Bottom Rules conditional formatting option. You can use this option to quickly identify the values at the top and/or bottom of your range. You can also use it to simply identify values that are above/below your range’s average.

3. Quickly Rank Your Data With Data Bars

If you are more graphically inclined, Data Bars are an invaluable conditional formatting option. With Data Bars, you can quickly and easily create mini “graphs” inside each cell in your range that allow you to see how individual values rank within your range (cells with larger values will have larger Data Bars and vice-versa). Data Bars are a great way to add a functional graphic to your analysis.

4. Add a Splash of Color With Color Scales

Color Scales work much like Data Bars, except they use different shades of color to indicate ranking rather than a bar graph. You can use the Color Scales conditional formatting option to quickly add a splash of color to your analysis while allowing your end-users to quickly see where individual items rank within the range.

Become a Master of Conditional Formatting

If you follow these four tips, you will be well on your way to learning how to master the conditional formatting feature in Excel. However, there is much more to conditional formatting than what is presented here.

To learn more, sign up for our free email newsletter, where you’ll get Excel tips and tricks delivered directly into your inbox. For a limited time, you’ll also receive a free Gantt Chart template when you sign up!

Microsoft_Excel_2013_logo.svg

Make an Excel Pivot Table in 2 Steps

Few skills are more important to master than the ability to create an Excel Pivot Table. If you know how to create an Excel Pivot Table, you can quickly summarize and analyze large datasets. You can also create dynamic tables and charts in order to quickly see different views of your data. Luckily, creating an Excel Pivot Table is pretty simple and can be accomplished in just two steps, allowing even the newest of Excel users to start creating complex data analysis.

Step 1: Select the Data You Want to Pivot

The first step is to simply define the data you want to include in your Excel Pivot Table. So, just highlight the data you want to include in the pivot, as in the example below:

Excel Pivot Table Selection

Step 2: Create Your Excel Pivot Table!

Then simply go up into the Insert Tab and select PivotTable, as in the example below:

Insert Excel Pivot Table

Just click “Ok” on the next screen, and you should get something that looks like this:

Excel Pivot Table Final

You have successfully created a Pivot Table! Now, you are ready to add fields and filters and begin analyzing your data! What you have created is the skeleton for a basic Pivot Table. Once you have a better understanding of how to arrange fields and filters in the table, you can create advanced (and dynamic) data analysis projects. With further training, you can also learn how to incorporate Pivot Charts into your Pivot Table reports. Pivot Charts and Pivot Tables function similarly and are extremely valuable if you need to model your data graphically. Excel Pivot Tables are extremely powerful data analysis tools if you know how to use them. The best way to start learning more about them is to simply find a reliable resource on the internet and sign up for some training classes.

To get started, start by signing up for our Desktop Secrets Newsletter, where you’ll receive Excel tips and tricks directly in your inbox in addition to exclusive discounts on our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Get a Free Gantt Chart Excel Template!

Creating and using a Gantt Chart Excel template is one of the most popular ways to manage, organize, and track projects. Learning how to create a Gantt Chart in Excel is a key skill for anyone who wants to manage projects more effectively. In this article, you’ll learn how to create your own Gantt Charts in Excel as well as receive a free, downloadable Gantt Chart Excel template.

First Steps

First, set up your project tasks in a table as in the picture below, with the name of the task in the first column, the start date (with 0 representing the start of the project) in the second column, and the duration (in days) in the last column.

Gantt Chart Step 1

Then you will want to highlight the data in your table and insert a Stacked Bar Chart, as in the picture below.

Gantt Chart Step 2

You should get a chart that looks like this:

Gannt Chart Excel Template Step 3

Formatting the Gantt Chart

First, select the legend (on the right-hand side of the chart) and delete it. Then go up into the Ribbon, select the Layout tab under the Chart Tools group, and select “Vertical (Category) Axis” from the drop down menu on the far left-hand side of the ribbon.

Gantt Chart Excel Template Step 4

Select “Format Selection”

Gantt Chart Excel Template Step 5

Put a checkmark in the “Categories in reverse order” box.

Gantt Chart Excel Template Step 6

To complete your Gantt Chart in Excel, go back to the dropdown box in the Layout tab of “Chart Tools” and select “Series ‘Start Date'”.

Gantt Chart Excel Template Step 7

Finally, go in the Format tab (under Chart Tools) and click “Shape Fill” and then “No Fill”.

Gantt Chart Excel Template Step 8

Your final product will look like this:

Gantt Chart Excel Template Final

Of course, you can play around with the formatting to make it look a little more interesting. You can add titles, change the style, pretty much anything you can do with a normal Excel chart. For our template, we added a title and applied Chart Style 27.

Gantt Chart Excel Template Final with Formatting

Get a Free Gantt Chart Excel Template!

Get your very own free Gantt Chart Excel template by simply signing up for our email newsletter! Once you sign up, you’ll get a free download of a template that you can customize for your own projects. In addition, you’ll also get valuable Excel tips and tricks directly in your inbox. Get your free Gantt Chart template now!

Microsoft_Excel_2013_logo.svg

Why You Should Learn to Use the Excel Index Match Function

The Excel Index Match function is one of Excel’s most flexible and powerful lookup functions and has several advantages over the traditional VLOOKUP function. The Excel Index Match function is much more flexible than the VLOOKUP function and is able to overcome many of the VLOOKUP’s structural limitations. Index Matching also requires less processing power from your computer, which is very important if you are building a spreadsheet with thousands of lines. However, very few people know how to create an Index Match function, much less use one. In the article below, I will show you how to create Excel Index Match functions and how you can use them to solve complicated lookup problems.

The Excel Index Match Function: A Summary

The Excel Index Match function is actually a combination of two different functions: INDEX and MATCH. The basic way it works is that you define the array with the item you want to pull in the INDEX function, and then you use the MATCH function to identify the row. Here’s the “official” Excel syntax:

=INDEX(Array,MATCH(Lookup_Value,Array,0))

Here’s the function in plain English:

=INDEX(Column-You-Will-Pull-Value-From,MATCH(Lookup-Value,Column-You-Will-Find-Lookup-Value-In,0))

How the Function Works in Real Life

The most important advantage of the Excel Index Match function is that, unlike the VLOOKUP function, you can use Index Matching to lookup values to the left and the right of your reference column. In the example below, if you wanted to pull the ID number of a particular fruit using the fruit name in a VLOOKUP, you couldn’t do it. You would have to move the ID column to the right of the Fruit column in order to avoid generating an error with a VLOOKUP.

Excel Index Match Example

However, if you use Index Matching (as in the example below), it is very easy to pull values to the left of your reference column.

Excel Index Match Example

In this example, the column we want to pull our final result (or value) from is Column A, ID. So, that goes into the first part of our function. Then our lookup value is simply D2, which contains the word Apples. Next, we define the column we want to find the lookup value in as Column B, Fruit. Finally, we set the last function option as 0, which tells Excel we want an exact match to our lookup value in the lookup. Here’s what our final formula looks like:

=INDEX(A:A,MATCH(D2,B:B,0))

The formula works by identifying the row number in the lookup column (in this example Column B) by finding the lookup value in that column. It then returns whatever row number the lookup value is in as the result. So, in this example, “Apple” was in Row 2 of the lookup column. So, the MATCH function returns a value of 2. Then, in the INDEX portion of the function, Excel takes the row number identified in the MATCH function (in our example, 2) and pulls whatever value is in row 2 of the column you want to pull a value out of. So, in this example, since the column we want to pull a value out of is Column A and the row number (defined by the MATCH function) is 2, Excel pulled the item in row 2 of Column A, “1”. Below is a graphic demonstration of how the function operates:

Excel Index Match Explanation

Once you are able to master Index Matching, you will be able to perform much more complicated data lookups than you would with a simple VLOOKUP.

Don’t Stop Here

There are many more valuable lookup functions that you can use in Excel beyond VLOOKUPs and Index Matching. Sign up for our free email newsletter to learn more about these functions and other advanced functionality in Excel. You’ll also get exclusive discounts for our training classes and webinars.

Microsoft_Excel_2013_logo.svg

3 Excel Date Functions Everyone Should Know

Knowing how to use a few of the many available Excel date functions is a useful skill if you have to do any sort of time tracking or reporting. Aside from using proper number formatting, it is also important to know how to use a few of the core Excel date functions. Knowing how to use these date functions will enable you to analyze date/time data in increasingly interesting and more complicated ways. If you learn the three Excel date functions outlined below, you will be well on your way to becoming an Excel power user.

1. Get Started with Excel Date Functions TODAY!

The TODAY and NOW Excel date functions are particularly useful if you need a dynamic function to automatically calculate the date and/or time every time you open or refresh a spreadsheet. The only difference between these two functions is that TODAY will simply return today’s date, while NOW will return the current date and time (calculated using the time settings on your computer). To use either of these functions, simply enter =TODAY() or =NOW() into a cell on your spreadsheet. There is no need to put anything inside the brackets of the function.

2. Determine Working Days With NETWORKDAYS

The best Excel date function to use to calculate the number of working days between two dates is NETWORKDAYS. The syntax is =NETWORKDAYS(Start-Date,End-Date,Holidays). You can enter the dates manually into the formula, but they have to be formatted using Excel’s special serial date formatting. It’s much easier to simply put your dates into two different cells and use cell references to pull them into the formula:

Excel Date Functions

Note that the exclusion of holidays from your calculation is optional. Any dates listed in the holidays section of the formula will be excluded from the working days count.

3. Determine a MONTH/DAY/YEAR or HOUR/MINUTE/SECOND

If you’re presented with a date, and you only need to pull out the particular month of that date, the easiest way to do that is with the MONTH Excel date function. If you use =MONTH(Date), Excel will return the numerical value of the month in the date you have indicated, as in the example below.

Excel Date Functions

The DAY/YEAR/HOUR/MINUTE/SECOND functions all work the same way. These functions are useful if you need to break out the data by segments (day, year, etc.) and are particularly helpful in Pivot Table filters when the standard date filters aren’t feasible.

Put Some Training on Your Calendar

This only scratches the surface of what you can do with Excel date functions. There are many more applications that will enable you to perform increasingly complex data analysis with Excel. To get started, signup for our free email newsletter, where you’ll get Excel tips and tricks directly in your inbox. You’ll also get exclusive discounts for our training classes and webinars.

Microsoft_Excel_2013_logo.svg

How to Land an Entry Level Business Analyst Job

Entry level business analyst jobs are not easy to obtain. However, the starting salary for an entry level business analyst is well worth the effort. The easiest way to gain an edge on the other job candidates is to build your Excel skills. In addition to learning how to use Excel, knowing how to showcase what you’ve learned is key to landing an entry level business analyst job. Follow our three tips below and, you’ll be well on your way to earning a spot at a Fortune 500 Company near you.

1. Make Your Resume Excel

As an applicant for an entry level business analyst position, you probably already have a well-refined resume. However, you don’t want to overlook the importance of including your Excel experience on your resume. When skimming through resumes, many employers look for key indicators such as the ability to create pivot tables or write macros. Try to avoid using such generic descriptors as “Advanced Excel User” or something similar. Don’t tell your potential employers that you know how to use Excel, show them! If you’ve used Excel in prior jobs or in projects for school, be sure to include that in the prior experience section of your resume. Including a sentence such as “Utilized Pivot Tables to analyze and slice data in order to prove…” will go a long way towards showing that you know how to use Excel and impressing the hiring manager for your entry level business analyst role.entry level business analyst

2. Get Certified!

In line with Tip #1 above, an easy way to demonstrate your Excel prowess is to obtain the official Microsoft certifications. The benefit of earning an Excel certification is two-fold. First, in the process of earning the certification, you will learn all about the core features of Excel and how to use them effectively in your everyday work. Then after you’ve earned the Excel certification, simply listing the certification on your resume will quickly and easily demonstrate your advanced Excel knowledge to prospective employers. Excel knowledge is key to landing an entry level business analyst position. Earning an Excel certification is an easy way to both obtain that knowledge and showcase it.

3. Build on Your Existing Knowledge

Whether you’re trying to land an entry level business analyst position or any other job, you should always look for ways to improve your existing skill set to remain competitive in whatever field you go into. Because Excel is such a complicated program and it has so many different uses, there is always something new you can learn about the program. So, whether you’re just starting out, or you were born with a spreadsheet in your hand, you can always learn something new.

Build Key Skills for an Entry Level Business Analyst Job for Free!

A great way to learn more about Excel is to sign-up for our free email newsletter. You’ll get valuable Excel tips right in your inbox, and you’ll also receive exclusive discounts to our classes and webinars!

Microsoft_Excel_2013_logo.svg

Getting Excel Help Like a Pro

Finding Excel help is often much easier than you might think. In addition to a variety of formal training classes, there are also several ways you can obtain Excel help on your own. One of the easiest ways to do that is using the internal help functions built into the Excel software. Learning how to use and navigate the Excel help built-in to the program is an important skill any serious student of Excel should learn. It will enable you to become more self-sufficient and also allow you to explore some of the more advanced functionality within Excel.

Good Excel Help is Easy to Find

To open the internal Excel help, simply navigate to the upper-right hand corner of your screen and click the question mark:

Excel Help

In the next window that pops up, simply type your question (or subject keyword) in the search bar. If we needed to know how to password protect an Excel workbook, we would simply type “password protect workbook” in the search bar and get these results:

Excel Help Password Protect

The first few results will take you to the “official” Microsoft help, either to the internal help files or Microsoft’s support website. The Excel help comes in a variety of different formats, allowing you to choose the format that is best suited for your situation. There are articles, videos, and even help forums, where you can ask and answer Excel help-related questions.

After the first few search results, the search will start to pull in Excel help from all over the internet. So, if you want to stick to the official Microsoft help, it’s generally best to use the first five or so search results. However, if you’re having trouble finding a solution to your problem, many of the websites that show up in the search results can be valuable alternative resources.

Even Experts Need Help Sometimes

Another great way to find solutions to your Excel help questions is to sign up for our free email newsletter, where you’ll get valuable Microsoft Office tips and tricks right in your inbox. You’ll also get exclusive offers for our upcoming training classes and webinars!

Microsoft_Excel_2013_logo.svg

How to Make a Graph in Excel: 3 Ways to Stand Out

Learning how to make a graph in Excel is relatively easy. However, when used properly, Excel graphs (or charts) can be a key contributor to the success (or failure) of your analysis or presentation. An Excel graph should complement your data and invite comparisons where possible. They can also be used to quickly summarize and display complicated data and demonstrate relationships between data. The following three tips will enable you to craft Excel graphs that are both useful and visually appealing.

1. Use Colors to Make it Look Good (But Not Awful)

When creating an Excel graph, most people fall into one of two camps. Either they simply throw a simple graph onto the page with the default settings:

How to Make a Graph in Excel

Or they go to the other extreme and change all of the settings (burning your eyes in the process):

How to make a graph in Excel

The best looking graphs usually fall somewhere in the middle:

How to make a graph in Excel

A good rule of thumb is to not change more than 3-4 of the formatting settings. Simply pick a few settings that will help make your data “pop”. The key is in moderation. If you follow this rule, your graphs will serve to strengthen your analysis (or presentation) and not distract from it.

2. Use Different Chart Types on the Same Graph

An easy way to invite meaningful comparisons in your Excel graphs is to use two different chart types within the same graph, as in the example below:

How to make a graph in Excel

This allows you to take two different sets of data and overlay them so that you can quickly demonstrate causal relationships (in this case, the relationship between the number of homework assignments returned and test scores). This is also a good way to save space if you find yourself running out of paper (or screen). However, you’ll only want to do this with chart types that will make sense. You wouldn’t want to try to overlay a pie chart on the above graph, for example. When done properly, using different chart types within the same graph allows your viewers to quickly see both the separate data series within your graph and the relationships between them.

3. Use the Secondary Axis Feature

One of the most under-taught aspects of charts is how to make a graph in Excel plotted against more than one y-axis. If you know how to make a graph in Excel using both the primary and secondary axis and you also know how to use different chart types within the same graph (Tip #2), you will be able to create very readable and very informative graphs, as in the example below.

How to make a graph in Excel

The advantage of the secondary axis is that it allows you to plot your different data series against two different numerical scales (one on the left and one on the right). In the example above, test scores are plotted against the left-hand axis and their numbers range from 0-120. However, the Assignments Returned data series is plotted against the right-hand axis and ranges from 0-12. This graph actually contains the same data as the graph in Tip #2, however it is easier to see how the number of assignments returned affects test scores because this chart uses the secondary axis.

Learn How to Make a Graph in Excel Using Our Tips

Excel offers many different options to customize your graphs. However, if you use our three tips above, you will produce graphs that both interesting and visually appealing.

To learn more about how to make a graph in Excel, sign up for our free email newsletter, where you’ll get valuable Microsoft Office tips and tricks right in your inbox. You’ll also get exclusive offers for our upcoming training classes and webinars!

Microsoft_Excel_2013_logo.svg

How to Password Protect Excel Workbooks and Secure Your Data

Password Protect Excel Workbooks In 3 Clicks

Many times you want to restrict who can access your spreadsheets. Luckily, Microsoft makes it easy to password protect Excel workbooks. Using the method above, you can password protect Excel workbooks and secure your data against unauthorized access. When you encrypt the workbook, only those with the password will be able to open or view the file, which allows you to control access to your data and also prevent someone from making unauthorized changes. However, it is also important to remember your password, because if you lose it, it is very difficult to recover. Watch the above video to learn how you can password protect Excel workbooks when you need to secure the file and how to remove passwords when encryption is no longer necessary.

Password Protect Excel WorkbooksIf the data in your Excel file is extremely sensitive, it is important to use secure passwords. Don’t use easy-to-guess passwords (like your birthday or even “password”) to password protect Excel files. You can use free tools on the internet to generate random, secure passwords. Norton (the company that produces Symantec anti-virus software) has a free password generator on their website. Use a resource such as this (or any other number of free resources on the internet) to password protect Excel files with secure, difficult-to-crack passwords. You can also layer several different passwords within your Excel file to further restrict access to sensitive data and provide additional security if your initial password is compromised. While beyond the scope of this video, you can learn more about how to secure your Excel files by attending one of our classes or webinars.

To learn about other ways of securing your Excel files, sign up for our free email newsletter, where you’ll get valuable Microsoft Office tips and tricks right in your inbox. You’ll also get exclusive offers for our upcoming training classes and webinars!