tips & tricks Archives - Page 4 of 5

How to Use a Custom Number Format in Excel

January 19, 2019

by howtoexcel with No Comment accounting formatting tips & tricks

There are many different options for formatting data in a spreadsheet. And there are even more available if you use a custom number format in Excel. That flexibility is important because it can be a bit frustrating if, for example, you want negative numbers to show up with a dollar sign as you have to use the currency format in that situation — which does not look very polished:

The positive and negative amounts look okay but I’d like to see a bit more spacing. But the bigger issue for me is the $0.00 formatting which can create a lot of noise if you’re looking at financials with lots of zeroes over the place (although I have a solution for this). It can divert your attention away from what you want to see — the cells that have non-zero values.

Creating a Custom Number Format

Although it may not be available by default, there is certainly a way to get a whole lot closer to the formatting that I want, and I’ll show you how. To start, you want to select the accounting format and then flip over to the Custom format (to do this right-click and select Format Cells). You’ll notice this is what the string looks like in the Type field:

The accounting format in microsoft excel.

This is what the accounting format looks like. The formatting is broken out into four main parts: positive, negative, zero, and text.

The string that appears until the first semi-colon is how the number will look when it is positive. Until the next semi-colon is the negative formatting, followed by if the value is zero and the last one is text.

Here is what the positive amount looks like in the accounting format:

_($* #,##0.00);(

The negative formatting looks very similar:

_($* (#,##0.00);

The main difference you’ll notice is the extra bracket “(” that is in the negative format. That is what puts the negative amounts in parentheses. Now, if I want to make this highlighted in red, all I would need to do is add [Red] right after the semicolon that indicates the end of the positive format:

($* #,##0.00);[Red]($* (#,##0.00);($* “-“??);(@_)

Upon doing that change, my number now comes up in red:

These are all the color options you can use:

Black
Blue
Cyan
Green
Magenta
Red
White
Yellow

There’s not any added customization you can do to these colors. And as you can imagine, many of these colors will be an eyesore on the default white background, and I’m not sure why you would even need to use the default black value. Blue, magenta, and red are the only ones that are easy to read and that won’t make you want to change the background color.

More Customization Options

For more complete customization, you’re better off looking at how to use conditional formatting.

If you need to make other tweaks to number formats what you can do is select the format and then switch over to the Custom section. Then you’ll see what that format looks like and you can test out what adjustments you’d like. Whether it’s adjusting the spacing or how the format looks like with a zero value, these are changes you can easily make and see what works through some trial and error.

If you’re interested in looking how to format dates, check out this post.

If you liked this How to Create a Custom Number Format in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

4 Biggest Annoyances of Pivot Tables and How to Fix Them

November 12, 2018

by howtoexcel with 2 Comments beginner pivot table tips & tricks

Pivot Tables are great tools, especially for quickly summarizing data and allowing you to avoid entering in complex formulas to do so. However, they aren’t perfect and definitely have their quirks about them.

Here are four things that most users aren’t crazy about, and how to fix them.

The layout

When you create a pivot table it puts the table into a layout that is less than optional for spreadsheets. It’s not only hard to use for any other purpose, but it’s also not the easiest format to read. I always prefer a format that resembles more of a spreadsheet itself, in a grid format, without the indented rows.

To change the format, go to the Design section of the PivotTable Tools and select Report Layout and select Show in Tabular Form

This is what the pivot table looks like after the change:

This might still look a little cluttered, but what you can also do is get rid of any subtotals by simply right-clicking on the Total and unchecking the Subtotal button, and then it’s a bit cleaner:

The table still needs a little work for me, and that brings me to the second issue:

Labels do not repeat

You’ll notice that the value in the date field only shows up once. And so if I had many entries for a given date there would be a lot of blank values in the first column. Again, this is not ideal for a large data set and so this is where I’d like to make a change as well.

I go back to the Report Layout section, except this time I select the option to Repeat All Item Labels

Now my table looks like this:

This is what I would hope the pivot table looks like right off the bat. Unfortunately, Microsoft does not agree and opts for the indented version with subtotal at every section and no repeating values.

But that’s not all, there’s another little nuance you may notice when it comes to pivot tables:

Formatting won’t stay consistent

You’ll notice that in the sales data, I have a total of 2158 for January 4th. Anytime you’re dealing with numbers in the thousands, you’ll probably want some formatting that shows a comma in there just to make the numbers more readable. However, if you simply select the entire column and change the formatting, it will revert back to the default if you refresh the pivot table after adding more data.

What you need to do is actually change the field settings. To do this, right click on any of the numbers in that column and select Value Field Settings.

From there, select Number Format and then select the format you want it to show up as. Doing this will ensure the formatting won’t change when you refresh the data.

Tip: if you routinely make these changes like I do, you may want to check out the add-in I created which will make all these changes for you at the click of a button.

This brings me to the fourth most irritating item when it comes to pivot tables:

GETPIVOTDATA

By default, when you reference a number in a pivot table, it will auto-generate a formula called GETPIVOTDATA. In most cases, you probably won’t find this helpful, especially if you just want to reference some pivot table values elsewhere in your workbook. If you just want to reference cell E4 and copy the formula down, you’ll need to adjust the pivot table settings, as the GETPIVOTDATA will make it a bit painful to accomplish this.

Back under the PivotTable Tools section, select the Analyze tab and on the Options menu on the left-hand side, uncheck where it says Generate GetPivotData

Now you can reference values from a pivot table the way you would any other cell in your workbook.

If you have any other frustrations with pivot tables, please share them in the comments.

Using Stacked Charts and Showing Totals

July 28, 2018

by admin with No Comment beginner charts formatting tips & tricks

A stacked chart in Excel allows users to take advantage of the best of both worlds: a column chart that shows period-over-period totals and a pie chart that can show what made up those totals. It can be a very useful chart, but knowing how to structure it is half the battle.

In my example, I pulled Alphabet’s earnings for the most recent four quarters. I wanted to show a) the period-over-period sales as well as where those sales went, and how much flowed through to the company’s operating income.

Below is the table that I used:

The key things is you want to make sure that all your categories add up to the amount that you’re trying to reconcile (in my case, it’s revenue).

Once the data is ready, select the data and insert a Stacked Column

In my example, the categories showed on the horizontal axis, which is not what I wanted, so in order to fix that, right click on the chart and press Select Data

From there, you want to hit the button to switch row/column:

This will give you a stacked chart. The problem, however, is that my total (revenue) is mixed into this, and that’s not going to give me the desired result. After all, I want to see where the revenue goes, not include the revenue in my categories.

To fix this, you’ll want to right click on one of your column charts and select Change Series Chart Type

That should take you to the Combo section. If it doesn’t, make sure to select it. All your series should show a stacked chart. You’ll want to change the revenue series to a Line chart. By doing so, it will not contribute to the stacked chart and now it’ll simply be made up of the other categories.

Next, add data labels for the line chart so now you’ll see the totals. To do this, right click on the line chart and click Add Data Labels

The problem is the labels show to the right, and it probably makes more sense for these labels to show above the stacked chart. Right click on any of the labels and select Format Data Labels

Make sure that for the label position, Above is selected

Now my chart is starting to come together:

Except I still have that line going over the top of the stacked charts. To get rid of the line, right click on the line chart and select Format Data Series

Select No line from the Line section

Now, select the Marker and make sure the fill option is set to No Fill

Now, I’ve gotten rid of the line completely:

At this point it just comes down to designing the chart how you want it. Some of the changes I made included:

Getting rid of ‘Revenue’ from the legend
Changing the color theme
Shrinking the gaps between the stacked charts

Formatting and Removing Errors and Zero Values

July 21, 2018

by admin with One Comment beginner errors formatting iferror match tips & tricks vlookup

Often times in a data set you’ll have to handle with errors that can wreck your data, especially if you need to do any analysis on it. There are several ways that you can handle errors so that they don’t show up in your data.

IFERROR

The first method is by using the IFERROR function, which allows you to easily replace the error with whatever you want in its place. If you want a numerical value, you may want to put in a 0, otherwise you can just leave it blank.

I’ve purposely added various types of errors to my data set:

Here is one of the formulas that’s causing an error:

=VLOOKUP(D7,L:L,1,FALSE)

In the above example, I could use the formula =IFERROR(VLOOKUP(D7,L:L,1,FALSE),””) to replace the error with a blank. I could also put a 0 in its place instead of the “”.

ISERROR

In older versions of Excel (2003 and earlier), the IFERROR function is not available. However, what you can use is a combination of the IF and ISERROR functions. To recreate the same formula as above, we can use the following:

=IF(ISERROR(VLOOKUP(D7,L:L,1,FALSE)),””,VLOOKUP(D7,L:L,1,FALSE))

The disadvantage of this method is you have to repeat your original argument. First, you are checking if the value is an error, if it isn’t, then you have to repeat the formula again to save the value. It’s not terribly efficient, and likely why we saw the IFERROR function introduced in newer versions of Excel.

Using the IFERROR or IF(ISERROR()) functions can be useful for eliminating errors, but sometimes it may not be helpful for dealing with specific ones. For example, if your cell is blank or it has an error and is made to look blank, you won’t be able to tell the difference just by looking at it. The danger is that you may assume it’s a different type of error.

ISNA

What you can also use is the ISNA() function, which can tell you if the cell returns the #N/A error. This way you can trap this error specifically, rather than everything that can be captured by the ISERROR() function.

Getting Rid of Zeros

If you’ve used a lot of error-handling functions and replace your errors with zeros, you could up with a lot of zero values on your spreadsheet:

The problem if you have a lot of zeros on your spreadsheet, is it can sometimes be a distraction away from what you really want to see – the non-zero values. There are two ways you can get rid of the eyesore:

1. Change the format to Accounting. Doing this will remove the zero values and replace them with a dash, which makes it a bit easier to skip over when doing a review:

However, you may not want to use the Accounting format, and that leads me to the other option:

2. Conditional Formatting. Refer to this post on how to setup rules for this. What I normally do in these cases is set the zero value cells to a light gray color font so that they do not attract your attention:

Common Types of Errors

Here’a list of some of the common types of errors you’ll find in Excel:

#REF: This is an error that you’ll incur if your range doesn’t go far enough and the error relates to your reference. For example, consider a VLOOKUP formula that extracts from column number five but you only specified a range that had four columns, that would result in a REF# error.

#N/A: You’ll get this error if your VLOOKUP or MATCH formula is correct, but the value you’re looking up isn’t found, and hence, not available. However, there’s other contexts it can apply to, and it just means that it wasn’t able to find the value you were looking for.

#VALUE: This error normally shows up when there is an issue with your actual calculation. For instance, if you’re trying to multiply a number by a field that has text.

These are just a few examples of the errors that you’ll encounter, but these are also likely the most common that you’ll come across.

Finding Partial Matches and Using Wildcards with VLOOKUP

July 1, 2018

by admin with No Comment intermediate tips & tricks vlookup wildcards

If you’re looking up data, often times just using a VLOOKUP function can be enough to get you your desired result. Sometimes, however, it doesn’t do enough, especially if you’re looking for a partial match.

While you can set VLOOKUP to pull an approximate match rather than an exact match, that may not provide you with the desired results, especially if you’re using text.

Consider the following situation where you’ve got a series of charges from your credit card statement and want to find a particular vendor:

In the above example, let’s assume I’m looking for McDonald’s and do a regular VLOOKUP and set the approximate match argument to true, my formula looks like this:

=VLOOKUP(“MCDONALD’S”,B:B,1,TRUE)

And the result I get is this

As you can see, it’s not what I was hoping for. Excel can’t figure out that I’m looking for the second result, and simply gives me the last one.

Using Numbers with the Approximate Match

The approximate match isn’t useless in VLOOKUP, in fact, when it comes to numbers, it can be very accurate.

Consider the following example:

If I want to find out the tax bracket that a given income level relates to, I’ll use this formula

=VLOOKUP(G11,D:E,2,TRUE)

Assume column D is the taxable income and column E is the tax bracket %, with the taxable income I input being in cell G11.

Using this formula, if I put an income of $0 in G11, my tax bracket is correctly returned as 0%. It’s not until I enter $25,000 that it will return 10%.

Excel understands numbers better than words, and so it knows that since $24,999 is not greater than or equal to $25,000, that it still belongs in the first tax bracket, the one that was 0% and started at $0.

If I enter $105,000 as my income amount, then it also correctly knows that I’m in the 25% tax bracket since that is the highest bracket in the list.

If, however, I don’t put the brackets in the correct order my results won’t be the same. In order for this type of calculation to work, you need to start from the lowest value to the highest.

How Can We Get Text to Work?

If you want your partial text matches to work, you’ll want to use wildcards. What you can do is add an asterisk before and after your search term, which will then return even a partial match. Here’s an example of how the updated formula might look from the first example:

=VLOOKUP(“*MCDONALD’S*”,B:B,1,FALSE)

This might look a bit confusing since now I’m actually not looking for an approximate match, but rather an exact one, as indicated by the FALSE argument at the end.

But because the asterisks will grab everything before and after my text, technically I do want it to match exactly, since it’ll search for my string as well as anything before and after it.

The result:

*Note that the formula is not case sensitive. Whether I type in MCDONALD’S or mcdonald’s, it would have no impact on my result.

As you can see, now I get the partial match that I was looking for. The danger, however, is that your partial string isn’t unique enough. If I were to use the word STORE as my string, I would get the first result that is a match, and in this case that would not be what I want.

Because VLOOKUP will return the value for the first time there was a result, you want to ensure its not a common string that will be found more than once.

Are Your Excel Files Too Big? 3 Ways You Can Bring Them Down in Size

May 28, 2018

by admin with No Comment formatting intermediate tips & tricks

If you’ve got a big Excel file (e.g. more than 10 mb) and you don’t have tens of thousands of rows, you may want to see what you can do about bringing that size down.

If you have lots of data, you may want to consider linking it using PowerPivot, although that’s a topic for a future post.

If your Excel file is unnecessarily large and you’re not sure why, there are three things you can do and check for to bring it down in size.

1. Check for objects

If you copied data from a webpage or somewhere that had images, you could have objects on your spreadsheet without ever realizing it. Many times they’ll appear invisible.

This has happened to me before and there’s a quick way to check and delete them if you do have them. Simply click F5, select Special, and then select Objects and hit OK.

If there aren’t any in your sheet you’ll get a message that none were found. If you don’t get that message then clicking delete will remove those that are on the sheet.

This will only look on the individual sheet you’re on so you may want to try this on all your sheets just to make sure.

2. Removing excess rows.

This on many cases is the culprit. People move data around and formatting is left behind and Excel holds data sometimes until the very last row. Again, this is an invisible problem that you won’t easily spot.

What you can do to determine if you have this problem is click on a cell in your data set somewhere and click CTRL + END. This will take you to the very bottom of your data. If it stops where it should, then you’re fine. If it takes you several thousand rows farther than you expect, or worse – to the bottom, then there’s a good chance you’ve found what’s making your spreadsheet all that bigger.

To fix the problem, select the rows from the very bottom all the way up to your last row. Delete these rows and then click save. The file size won’t update until you click save.

Tip: if you have lots of worksheets to go through, user CTRL + PG UP/PG DOWN to cycle through the different sheets and then hit CTRL + END on each one and you can quickly see if any sheet has the problem.

3. Remove unneeded columns

If you really just have too much data, consider deleting excess columns. While in many cases people think of removing unneeded rows, they neglect the impact that columns have. If you’ve got tens of thousands of rows, even deleting one column will remove that many data points. Multiply that by how many columns are unnecessary and the data savings will quickly add up, and your file size will see a noticeable decrease.

These are three of the most common reasons your file might be bigger than it needs to be. Often times people think having a macro is going to do it, but that’s not the case. Usually it’s just having lots of data and doing calculations on all that data will take a big chunk of your computer’s resources

5 Useful Excel Shortcuts That Are Underutilized

April 29, 2018

by admin with No Comment beginner shortcuts tips & tricks

There are a lot of ways you can speed up data entry and analysis in Excel, and while most people know about copying and pasting and how to undo actions, there are some useful ones that many users aren’t familiar with.

This list will go over five lesser-known, but useful shortcuts.

1. CTRL + 1 : Formatting cells.

If you find yourself changing the formatting of cells often then using this shortcut could help save some time rather than right clicking format cells. It may not be a huge time saver but if you’re not using the mouse then it’s an easier way to access that menu.

2. CTRL + F1 : Toggle the ribbon

When you’re working with a lot of data often times you’ll need just a bit more space, and hiding the ribbon can help give you see more rows on your page. You can also double-click on the active tab’s name and it will accomplish the same result.

3. CTRL + ~ : Show formulas

If you’re editing a spreadsheet and want to quickly find where there are formulas versus which cells are hard-coded, this is an easy way to show you what is actually entered in the cell, as opposed to what the end value is. The ~ button is normally located next to the 1 on your keyboard.

4. CTRL + ] and CTRL + [ : Showing dependents and precedents

These shortcuts are useful because if you click on a cell that has a formula you can see if there are other cells that are dependent on the cell for inputs somewhere else, or which cells feed into this one. CTRL + [ will show you which cells the active cell depends on, and where its inputs come from, while CTRL + ] will show you if the active cell is used in calculations elsewhere.

You can hit tab to cycle through all the cells that have been highlighted.

It will not show you all the cells if the related cells span multiple tabs, but it can be helpful when looking at formulas and inputs in all other cases.

5. ALT + F1 : Insert a chart

This shortcut can allow you to quickly launch a chart based on the data that you’ve selected. This again may not be a big time saver, but if you’re on a different tab or

Hide Tabs Using VBA to Ensure Users Cannot Unhide Them

September 7, 2017

by admin with One Comment tips & tricks vba

When you’re creating a template in Excel for other users often times there is information in the backend that you’d prefer users not be able to access or modify. In some cases it might be preferable to just hide the data entirely, especially if it contains sensitive information.

The easiest way to hide a tab in Excel is simply to right click on a tab and click hide.

The problem with this approach is as easy as it is to hide you can unhide it as well, which many users know how to do, and just involves right-clicking on a tab and clicking Unhide

After that you can see all the sheets that are hidden and by select the sheet and clicking OK you can unhide it.

You could protect your spreadsheet and prevent users from changing the structure but that might not be preferable either as it involves password protection and will not allow users to insert, copy, or even rename worksheets. If they don’t need this functionality then the solution might work for you.

To protect your workbook simply select the Review tab and click on the Protect Workbook button and you’ll see a pop up where you can protect the structure. Make sure to just tick off Structure and enter a password and click OK.

Another way to hide tabs is through one line of VBA code. If I wanted to hide Sheet1 I could use the following code:

Worksheets(“Sheet1”).Visible = xlVeryHidden

If your sheet is named something else then you would just change Sheet1 to the name of the sheet you want to hide.

Now when I go back to my spreadsheet and right click unhide:

Unhide isn’t even an option because there are no tabs that can be unhidden. If I hid another tab without using VBA then only that one would be visible, but the one using the code I used above would not show.

If you want to unhide the tab, the code to unhide is as follows:

Worksheets(“Sheet1”).Visible = True

What you could do is have two different procedures, one to hide a tab and another to unhide it. Then you can assign a shortcut key to each procedure. This will easily allow you to hide and unhide any tabs that you want to be invisible.

If you are not familiar with VBA check out one of my first posts on how to insert code and assign shortcut keys to a macro.

Formatting Charts to Make Them More Appealing

July 5, 2017

by admin with No Comment charts formatting intermediate tips & tricks

Excel makes it easy to convert a data set into a chart. The problem is that often the default chart settings aren’t the greatest. Below I have some sample data that I will convert into a chart:

If I click on the data and go on the Insert tab and click on a new Column Chart it will create the following chart for me (this may vary based on which version of Excel you are using):

There are a number of things that don’t appeal to me here that I am going to change:
– Gridlines are a little darker and more prominent than they need to be
– Gridlines stretch past the axis
– The legend is off to the side, which takes up chart space
– The border around the chart itself
– The gaps are a bit big
– The flat look of the chart

These may appear minor issues but in terms of presentation they can make a big difference. First I will start with the grid lines.

Formatting Gridlines

If I click on any of the gridlines I can right-click and select Format Gridlines. Under Line Color, the color is set to Automatic. I can change this by selecting Solid line.

I am going to change the color to grey so that it does not stand out as much.

Next I will have to format the axis to stop the gridlines from going past the axis. To do this I click on one of the axis labels to select them and again right-click and select Format Axis

From there, under the Axis Options there is a drop-down option for Major tick mark type. By default it is set to Outside. I am going to change this to None in order to remove it.

I will also change the Line Color here to match the grey from the gridlines. I repeat these steps for the other axis to get rid of the tick marks there as well.

Formatting the Legend and Adding a Chart Title

Next, I will change the legend so that it shows at the bottom of the chart. This is an easy fix and all I need to do is select the Layout tab from under the Chart Tools section of the ribbon. From there I select Legend and choose Show Legend at Bottom.

To the left of the Legend drop down is a section for Chart Title. This is where you can select how you want your title to appear.

If you select Centered Overlay Title you don’t lose chart space but then your title is overlapping with your chart. Above Chart will put the title above the actual chart so that there is no overlap.

Removing the Border

Next, I am going to remove the border around the chart itself. To do so, I need to right-click somewhere on the white space that isn’t on the plot area. Somewhere near an axis or the legend will work. Then I can select Format Chart Area.

If I select Border Color I can change the setting from Automatic to No line to remove the border.

It may look a little odd if your gridlines are showing so you may want the outline. However unless you print with gridlines, then the chart will blend in better without them. Below is an example of the two charts with and without borders in print preview mode:

Shrinking the Gaps

Lastly, I will shrink the gaps in the chart. To do this I will right-click on any of the columns and select Format Data Series.

Under Series Options there is a section for Gap Width. The default is 150%. I normally set this to 50%.

Changing Colors/Effects

If you wanted to change the colors of the chart you can do so individually or just change the theme. To change the theme go under Chart Tools and this time select the Design tab.

Changing the theme will undo the changes I have made to the gridline colors so if you do those changes you will want to change the theme first.

You don’t have to select a theme, you can change colors one by one. To change the color of an individual series you can do so by right-clicking on one of the columns and select Format Data Series and change the fill color under the Fill section.

Instead, what I am going to do is adjust the shadows. Right now they look flat, and I want a bit of an elevated effect. While still in the above menu I can select the Shadow option. If I click on the drop down in the Presets field, I will have a number of shadow options. I don’t use the inner shadows since they make the columns a bit dark, and the outer ones leave too long of a shadow. In this case I select the Offset Left option.

This is what my chart looks like now after all the changes:

Saving a New Template

Rather than making these changes every time I can save my changes to a template. To do so, just click on Save As Template which is under the Design tab in Chart Tools. Then just assign a name and your template is saved.

If you want to use your template again simply when select chart types select the Templates folder and you will see it there.

Use Goal Seek to Avoid Trial and Error

March 7, 2017

by admin with No Comment goalseek intermediate tips & tricks

Excel’s what-if-analysis options aren’t the greatest, but one option which is very useful and can save time is Goal Seek. What Goal Seek effectively does is it can do trial and error for you in seconds, and be much more precise than doing it manually.

In many examples you could use algebra to get to your answer but if it’s a one-time calculation perhaps it’s not worth the trouble of going that route. For example, suppose I had an investment of $1,234 and wanted to know what average return would be needed for it to grow to $10,000 after 10 years. This can certainly be done with algebra, but Goal Seek can also do it. Below, I have entered my inputs in yellow.

My ending balance is equal to the following formula : =C4*(1+C5)^C6

C4 = Investment

C5 = Required Rate of Return

C6 = Years

Next, I will select Goal Seek from the Data tab under What-If Analysis

The next screen prompts me to enter which cell I want to set to which value, and which cell I want changed to accomplish this (the variable). In my example I use C7 for the set cell box (this is my desired ending balance), the value is 10,000, and by changing cell I enter my required rate of return (which is C5).

After clicking OK it fills in the required rate of return as 0.23273147. It also sets the value in C5 to this amount as well. If I hit OK on the next screen the value in C5 will keep this value, if I cancel, it will revert to what was there before – nothing.

This was a simple example but there are also more complex ones it can be used for. Another example could be an amortization table. The table below has a principal amount owing of 10,000, an interest rate of 5%, a payment of 550 per month. This is what it looks like:

So it will take roughly 19 payments to for this amount to be paid off under the terms described. But let’s say I wanted to know precisely what interest rate would have to be applied to make the ending balance at payment number 20 equal 0. I don’t want an extra payment for the balance to be included at the end, just so that it perfectly matches 0 after payment 20.

To do this, I go back to Goal Seek. For the first box (set cell) which is what I want my result to be – I select the ending balance at payment 20 – currently it is (579.34). This is cell F27 on my spreadsheet. The second box is what result I want, which in this case is 0. Lastly, my variable, or the cell I want changed to make this work – that is my interest rate, which is in cell C4 – currently it is at 5%. These are what my inputs look like:

Once I hit OK, I get the following:

You’ll notice now my target value matches my current value and the Goal Seek was successful. It has changed my interest rate to 11% – not exactly 11%, 11.1048262490731%. A very precise percentage that has allowed my table to reach 0.

Goal Seek can be used in a variety of different circumstances as you can see. As long as you have a formula like in the above two cases that the change cell (e.g. variable) directly effects, then it should work. I could have selected the payment as my change cell rather than the interest rate and my result would be as follows:

It would indicate a rounded payment of 522.16 (exact amount of 522.162995552194) would be needed to pay off after 20 payments with the interest rate staying at 5%.

The one limitation of Goal Seek is that you can only use one variable. But beyond that it does it very well and can help save you a lot of time.