Microsoft_Excel_2013_logo.svg

Create a Graph: 4 Things You Didn’t Know

When you go to create a graph in Excel, there are many enhancements that you can use that you may not have considered using before. There are a variety of functions and formatting changes that you can use when you create a graph that will make your graphs more interesting and more useful. Listed below are the top four features that you can use to really make your graphs pop!

1. Changing Scales

When you first create a graph, Excel automatically sets the scale on both the horizontal and vertical axis of your chart. However, the default setting may not be the view that provides you with the most informative view of your data. If you change the scales, you can highlight smaller differences in the data or draw attention to trends more precisely. Luckily, Excel makes this easy to do. All you need to do is right click on an axis and then select Format Axis. The Axis Options tab provides you with ready access to change the minimum and maximum bounds of your graph to whatever values you need.

2. Adding/Removing Gridlines

Gridlines are often overlooked when people go to create a graph. However, when used effectively, gridlines can help you draw attention to a particular aspect of the chart or make it easier to read. Of course, it’s easy to over-do it with gridlines too. It’s generally best to use either horizontal or vertical gridlines, but not both. The easiest way to access the gridlines settings is to simply navigate to the Layout Tab of the Chart Tools section of the Ribbon.

3. Data Labels

Data labels are very useful for drawing attention to particular pieces of data in your graph. While it is not usually necessary to label every single data point on a chart, a few, carefully-placed data labels can help your readers zero-in on the information you are trying to convey in the graph. When you create a graph, the easiest way to add data labels is to simply use the Data Labels button on the Ribbon. This will add data labels to all of your data by default. Simply click on the labels you don’t need and delete them to make your graph easier to read.

4. Changing Colors

Don’t underestimate the power of color changes when you create a graph. Most people don’t change the default colorcreate a graph funny settings, so everyone’s graphs end up looking more or less the same. To make your graph “pop”, simply change a couple of the colors and make the chart more aesthetically pleasing. You can also use colors creatively to draw attention to particular parts of the graph (highlighting something in red or yellow, for example). You can change the color of everything from the outline of the bars on your bar graph to the background of the key by accessing the formatting options for whatever part of the chart you want to change. Simply right click on the part of the graph you want to change and select the format option.

Create a Graph, Learn a New Skill

Now that you know a couple new tricks to use the next time you create a graph in Excel, continue your education by signing up for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Pivot Table Basics

Pivot Tables are some of the most flexible and powerful tools you can build with Excel. But if you’ve never worked with Pivot Tables before, you may be wondering what you can actually use them for. In this article, we’ll cover some of the most popular ways to use Pivot Tables. Once you know when to use a Pivot Table, you can learn how to build one.

1. Use a Pivot Table to Summarize Data

At their most basic level, Pivot Tables are used to summarize data. Especially with larger data sets, Pivot Tables are very good at allowing you to slice and dice your data anyway you need. Regardless of whether you have a list of scores and need to determine the percentage of your class that obtained A’s, B’s, etc. or you have a company’s salary data and want to determine what the average pay for each office location is, Pivot Tables can allow you to do all of this and more. Once you have the basics down, you can also learn how to use a Pivot Table to create more complicated summaries or run more advanced calculations.pivot table joke

2. They Come as Both Tables and Graphs

Pivot Tables also have a very flexible layout. You can either structure your summaries as traditional tables or you can summarize your data by way of a graph. You can even create a linked table and graph, where any changes in the table are immediately reflected in the graph. In this regard, Pivot Tables are very flexible and allow you to structure them to whatever your presentation needs are.

3. A Pivot Table Can Count, Add, Divide, and More!

The number of ways that you can summarize your data is almost endless. Whether you need a straight-forward count of the number of people who work for a particular manager or you need to perform some sort of mathematical function on your data (add, subtract, divide, multiply, etc.), a Pivot Table can accommodate all of these needs. You can also create percentiles with your data and calculate the percentage of people at your company who qualified for a particular bonus level. All of this is possible with a few quick clicks in your Pivot Table.

Get even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Obtain an Excel Sum Automatically!

Obtaining an Excel Sum (or Average, Count, Min, etc.) automatically is pretty easy. You no longer have to worry about entering in strings of cell references or clicking and dragging over the numbers you want to add. Using AutoSum is quick and easy and, it’s a great way to speed up simple Excel calculations. All you need is a column (or row) of numbers and you can get an Excel sum automatically.

Calculate an Excel Sum with AutoSum!

To start, you’ll need a column of numbers (a row of numbers works just as well), as in the picture below. Note that your column/row can be anywhere on your spreadsheet.

Excel Sum Column

Then simply click on the cell at the bottom of your column (or at the end of your row). This will be the cell where your Excel Sum will be calculated. Once you’ve selected the proper cell, simply navigate up to the Home Tab. The AutoSum function is located on the far right-hand side of the Ribbon.

Excel Sum AutoSum

Simply click the AutoSum button, and your screen will look like this.

Excel Sum Final

Excel automatically determines the cell range for you by including all of the adjacent cells directly above the cell you clicked on. It also gives you a visual representation of what the formula will include by highlighting the included cells with a dotted line. Simply press the Enter key to accept the formula and calculate the sum.

Excel Sum Calculation

And you’re done! Not that if you want something other than an Excel Sum, simply click the drop down arrow next to the AutoSum button. Some of the more common choices include Average, Count, Min, Max, but you can use this technique with many more formulas.

Get even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

3 Excel Formulas You Must Know For Your Next Interview

As you prepare for your next job interview or promotion, there are three Excel formulas that you must know if you want to impress your interviewer. These three Excel formulas are simple to learn, easy to implement into almost any project, and sure to impress your prospective boss. In addition, these formulas are great time-savers and will make your job easier, wherever you end up!

1. VLOOKUP

This is one of the most crucial Excel formulas to learn, yet it is also one that a lot of people have trouble mastering. So, if you can master this formula, it’s a good way to showcase your Excel skills. Once you know how to use it, you’ll also find that VLOOKUP has a broad range of uses and numerous applications. Knowing how to use the VLOOKUP formula is a foundational Excel skill that will open many doors for you.

2. SUMIF/COUNTIF

Technically, SUMIF and COUNTIF are two different Excel formulas, but their structure and function is similar. Rather than doing a simple add or count, these two functions allow you to apply conditions to your formula. So, if you have a column of data, and you only want to add or count lines that meet a particular set of criteria, you can use these two functions to accomplish that. Aside from Excel formulas job interviewshowing your future boss that you know how to use Excel, demonstrating an understanding of these two Excel formulas shows that you know how to use Excel efficiently. These two formulas allow you to integrate a conditional (if) statement and an addition command into one formula, turning what would normally be a two-step process into a one-step process. This saves you time and allows you to work more efficiently. If you know how to use these Excel formulas (and others like) them, you will demonstrate that you are a smart Excel user.

3. IF/AND/OR

Again, this is not technically “one” Excel formula, but the use and function of these three formulas is very similar. IF, AND, and OR form the basis of conditional statements in Excel. These three formulas allow you to evaluate data and other formulas against a set of given criteria. They allow you segregate your data and to pull out and evaluate only that data which you really need. If you know how to use these three Excel formulas, you can build extensive logic trees and other complicated formula strings. If you can demonstrate to a future boss that you can use these three formulas proficiently and creatively, you will be sure to impress.

Learn More Than Just How to Use  Excel Formulas

Get even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Excel TRIM, SUBSTITUTE, and Removing Spaces

The Excel TRIM function makes removing spaces from the ends of words very easy. In fact, it is frequently necessary to add or remove spaces from strings of text in Excel. Using the Excel TRIM function or the SUBSTITUTE function makes adding (and especially removing) these spaces very easy to do. If you know how to use both of these functions, you will never have to go through a spreadsheet and manually remove the extra spaces in cells ever again!

The Excel TRIM Function

Excel TrimThe Excel TRIM function is very simple to use. The syntax is simply =TRIM(Cell). When you use TRIM, it will remove all of the extra spaces on either side of a string of text in a cell. So, if you have some data in a cell that is preceded by a space or followed by a space, you can use TRIM to remove those spaces quickly. However, note that TRIM is somewhat limited, in that it will not remove spaces that occur between words. So, if your string contains a series of words separated by spaces, TRIM will only remove the spaces that occur before the first word and after the last word.

The Excel SUBSTITUTE Function

The SUBSTITUTE function is similar to the TRIM function, but it is much more flexible. With SUBSTITUTE, you can remove spaces from anywhere within a string of text (including between different words, unlike TRIM). You can also use SUBSTITUTE to add/remove more than just spaces. You can use it to swap out letters, numbers, or even whole words! The syntax is also very simple. It is =SUBSTITUTE(Cell,Old-Text,New-Text). So, if you wanted to remove all of the spaces from cell A1, you would use =SUBSTITUTE(A1,” “,””). If you wanted to remove all of the “A”s from A1 and replace them with “B”s, you would change it to =SUBSTITUTE(A1,”A”,”B”). Note that capitalization counts here.

Using the Excel TRIM and SUBSTITUTE functions makes adding and removing spaces, letters, numbers, and words quick and easy. Used properly, these two functions can make cleaning up your data a breeze.

Learn even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Excel INDIRECT Formula – How and Why to Use It

The Excel INDIRECT formula is a very flexible and powerful formula that allows you to make your formula references dynamic. In its simplest form, the Excel INDIRECT formula allows you to put the reference for one cell inside another, and then obtain the data from the first cell by referencing the formula. So, if your formula reads =INDIRECT(A1) and the value of A1 is “B1”, then the formula will return the value of B1. In effect, the INDIRECT formula converts any string of text into a cell reference. Creating dynamic cell references is where this formula is most useful.

How to Use the Excel INDIRECT Formula

To see how the Excel INDIRECT formula works with cell references in practice, consider the example of a workbook with multiple tabs, with different (class) names, where each tab looks like this (with scores filled in for each question):

excel indirect example

If you wanted to create a master summary table (as in the example below), where each class is listed out on the left-hand side of the table and a summary of their scores is presented on the right, you could use the INDIRECT formula to accomplish this fairly easily.

excel indirect example 2

You can use the Excel INDIRECT formula to turn each of the class names in the master summary table into a tab and cell reference. This will allow you to pull in each of the values from the separate worksheets without a lot of cutting and pasting. To pull in the data for the first question, simply insert the formula, =INDIRECT(A2&”! B2″). The INDIRECT formula will then return the value in A2 (which is “Class1”) and merge that with the concatenation “! B2” (we’re using cell B2 because that is the cell that contains the score value for Question1). This results in the cell reference “Class1! B2”. So, Excel will pull the value from cell B2 on the Class1 sheet into the master summary table. Simply copy the formula down to the remaining rows to pull in the values for the remaining sheets. As you can see, using the Excel INDIRECT formula is a quick and easy way to create dynamic cell references that allow you to pull in data from multiple sheets without having to directly specify the sheet names in the formula.

Learn even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Excel Formulas Not Working? Decode the Code!

Excel Formulas Not Working? Not sure what that error message means? In the course of compiling an Excel spreadsheet, you are going to run into formula errors. In order to fix the errors with your formulas, you have to know what that error message is telling you. What follows is a list of the most commonly encountered error messages you will encounter in Excel. Simply bookmark this page and refer back to it the next time you run into an unfamiliar error message or you aren’t sure how to fix your formula.

Excel Formulas Not Working? 8 Most Common Formula Errors

#DIV/0!

This error occurs when you attempt to divide a number by 0. Since this is mathematically impossible, Excel returns this error.

#N/A

This error occurs when your formula references a value that does not exist. For example, if you attempt to use a VLOOKUP formula that looks like =VLOOKUP(“USA”,A:B,1,False), but “USA” does not appear anywhere in Column A, then your formula will return the #N/A error.

#REF!

This error occurs when your formula contains an invalid cell reference. This could occur because you either mistyped the cell reference (i.e., you reference cell 1A instead of A1), or because you referenced a Named Range that does not exist.

#VALUE!

This error occurs when one (or more) of the values referenced in your formula is of the wrong type. For example, if you attempt to add a cell with text in it to another cell (e.g. =A1+B1, where the value of A1=”USA”), you will receive this error.Excel formulas not working

#NAME?

This error occurs when Excel is unable to recognize a text reference within a formula. This can be either a formula name that Excel doesn’t recognize or a misspelled Named Range reference. The most common cause of this error is a misspelled formula name (as in “VLOKUP” instead of “VLOOKUP”).

#NUM!

This error occurs when your formula contains an invalid number. This could either be a number that does not exist (as in the square root of a negative number) or a number that exceeds Excel’s processing ability.

#NULL!

This error is fairly rare, but most often occurs when you reference improper range references (as in where you’re trying to refer to two non-intersecting ranges).

#####

This error occurs when a number is so long it cannot fit in the cell. Simply re-size the column width of the cell to resolve the error. You will also see this error if you make a date negative.

Once you know why your Excel formulas are not working, you can start figuring out how to fix them. Many times this involves a lot of trial and error (especially if you are working with a long, complicated formula). But once you know why your Excel formulas are not working, you will be well on your way to figuring out how to fix them.

Excel Formulas Not Working? Need Help With Excel?

To learn even more free tips and tricks, sign up for our free email newsletter! You’ll learn all kinds of new and useful Excel tips and also receive exclusive discounts on our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Excel to PDF Converter – No Download Required!

Finding an Excel to PDF converter used to be something of a challenge. When you type “Excel to PDF converter” into Google, you’re faced with a deluge of various “free” and paid options. Then once you have gone through all of the hassle of picking out a converter program and downloading and installing it on your computer, you have to deal with the hassle of figuring out how to use it. Each program worked a little differently and, none of them had the same settings or options. It was a major hassle to just produce a PDF file from an Excel file.

Enter Excel’s Excel to PDF Converter

Luckily, we are no longer faced with such challenges, as Microsoft has created a built-in Excel to PDF converter in both Excel 2010 and Excel 2013. The easiest way to covert an Excel file into a PDF file is to simple go to the File Tab and click “Save & Send”. Then simply choose the “Send as PDF” option.

Excel to PDF Converter

This will prompt Excel to automatically convert the open file into a PDF document and attach it to an email in your default email program. From there, you can simply send the document to whoever you want. If you need the PDF for your own use (and you’re using Outlook), you can simply click and drag the PDF file into any available folder on your computer. This is, by far, the easiest way to convert an Excel file into a PDF file. Excel’s built-in Excel to PDF converter takes literally seconds to use, and there are no downloads from sketchy websites or complicated installations involved. Simply click the “Send as PDF” button and go!

To learn even more free tips and tricks, sign up for our free email newsletter! You’ll learn all kinds of new and useful Excel tips and also receive exclusive discounts on our upcoming training classes.

Microsoft_Excel_2013_logo.svg

Get a List of Holidays in Excel!

Creating a list of holidays in Excel is fairly straight-forward to accomplish with a nesting of the CHOOSE, WEEKDAY, and DATE functions. Using these three functions, you can build formulas in Excel that will automatically tell you when all of the holidays are for a given year and what day of the week each holiday falls on. Some holidays are pretty easy to determine. For example, Halloween occurs on the same day every year. But for the holidays that move every year (holidays like Thanksgiving, which occurs on the 4th Thursday of November), the solution is a little more complicated. At the end of this article, we’ll provide you with your very own Holiday Template, or you can use the information below to build your own, customized list of holidays.Santa Claus Using Laptop

The first step in building a list of holidays in Excel is in understanding how the CHOOSE, WEEKDAY, and DATE functions work. The DATE function simply returns the date (in Excel date format) from a value you specify. The syntax is DATE=(Year,Month,Day). So, all you need to do is specify the year, month, and day of the date you need. WEEKDAY works on a similar premise. It returns a value between 1 and 7 (1= Sunday, 2= Monday, etc.) based on a date that you specify. So, all you need to do is insert a date into the WEEKDAY formula, and it will tell you the day of the week that particular date falls on.

The key function to creating your list of holidays is the CHOOSE function. The way this function works is that you specify a list of options and then tell Excel which option you’d like to pick. The syntax is =CHOOSE(Option_You_Want,Option1,Option2,Option3,…). The first value in the function will be a positive number. So, if you put a “1” in there, the formula will return Option1. If you put a “2”, it will return Option2.

So, if you wanted to calculate the date that Martin Luther King, Jr. Day falls on in a given year, and your year is provided in cell B2, your formula would look like this:
List of Holidays Formula

This formula will determine what day of the week the first of the month falls on, and then add 14 days (since the holiday never falls on the first or second Tuesday of the month), and then adds additional days based on what day of the week the first of the month is. So, if the first of the month is a Sunday, the formula will add 14 to Jan. 1 (bringing you to Jan. 15) and then add 1 to that to get you from Sunday to the third Monday. The result is Jan. 16. You can use this same formula structure to build out your full list of holidays, especially for holidays that fall on a particular day of the month rather than a particular date.

Get Your Free List of Holidays Now!

Download a free template to create your own list of holidays! And don’t forget to sign up for our free email list, where you’ll receive free Excel tips and tricks directly in your inbox and exclusive discounts for our upcoming training classes.

 

Microsoft_Excel_2013_logo.svg

3 Ways to Use Excel Gridlines

Excel gridlines are an easy way to quickly spruce up your spreadsheet. Not only are they a great way to draw attention to particular areas of your spreadsheet, Excel gridlines can also aid you when you are inputting data into your workbook. Excel makes it easy to turn the gridlines on and off and also to change the way they look, which allows you maximum flexibility. This makes Excel gridlines readily adaptable to almost any application.

1. The Default View

Most people are already familiar with the default view of Excel gridlines. The familiar light grey grid makes it easy to organize and analyze data and is best suited for simple data entry or analysis projects or for situations where you won’t be presenting the spreadsheet to an external customer. So, for simple, internal projects, you probably won’t see a need to change the gridline appearance from the default view.

2. Vanishing Excel Gridlines

However, if you are planning on presenting your data analysis to an external customer or client and want to convey a slightly cleaner appearance, you can simply turn the gridlines off. To do this, go into the View Tab, and under the Show group, uncheck the box next to “Gridlines”. Your spreadsheet will then look like this.

Excel Gridlines

As you can see, once you uncheck that box, Excel removes the gridlines from the sheet, which allows you to present your data against a white background (though you can change the color if you wish). This is an easy way to make a clean, professional data analysis presentation.

3. Border Settings

If you want to do something a little more complicated than simply turning the gridlines on and off, you can do that through Excel’s border settings, which are found on the Home Tab in the Font group.

Excel Gridlines Borders

Using this menu of options, you can change the thickness of the gridlines, their colors, change them into dashes, symbols, etc. These options allow you the ability to change any aspect of the gridlines that you want, allowing you maximum freedom to customize them to fit any situation. These features are mostly valuable where unique formatting is necessary to emphasize the underlying data analysis and is very useful when you are getting ready to present your data

To learn more helpful Excel tips and tricks, be sure to sign up for our free newsletter! You’ll get free help articles directly in your inbox and be among the first to receive exclusive discounts to our training classes.