intermediate – Page 5 – HowtoExcel.net

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

How to Use an Advanced Filter

March 10, 2018

by admin with No Comment filter intermediate

There are many ways you can filter data in Excel, and in this post I’ll cover the Advanced Filter.

Using the Advanced Filter will allow you to set a criteria in place for your filter and then put the results in a separate section or tab. The benefit from a normal filter where you select your criteria using drop downs is that it won’t affect the rest of the data.

If you want to follow along with my example, I have the sample database file that I’m going to use available here.

Filtering the Data Using One Criteria

First up, I’m going to look at Sales Rep A’s sales for Product A.

For the Advanced Filter to work, there are two sections that need to be ready: the criteria, and the extract.

For the criteria, you need to have the fields that you want for your filter entered. In my example I only am looking for Rep A’s Product A sales, so I only enter that criteria and don’t enter any other fields.

In the extract section, which you’ll see just below the criteria section above, I enter the fields I want to see. I don’t have to list all the fields, just the ones that I want to see. Please note the field names need to be an exact match with those in your data set. If they are not, the filter will not work as expected.

Now, to execute the Advanced Filter I’ll click on the Advanced button that is under the Data tab next to the Filter button.

After doing so, you’ll be prompted to enter two sections:

In the List range you want to enter your table, or the data you want to filter. In the Criteria Range you will select the fields you want to filter along with the criteria you want to match.

You can either Filter the list, in-place which will effectively do the same job as if you filtered your data using the drop down options. If you select Copy to another location then the third field, Copy to, will be available for input and this is where you will select your extract fields.

These are what my inputs look like:

After I click OK, my results are as follows:

This looks the same as if I were to filter using the drop down, but the benefit is I am effectively copying the data to another location.

Using Multiple Criteria

In the previous example, I used an Advanced Filter just using one set of criteria. In this example, I’ll add another to create a more complex filter.

When using the criteria in an Advanced Filter, if criteria are on the same line it is the same as using the AND operator, while if the criteria are on different lines it is the equivalent of using OR.

In the first example, Rep A and Product A were on the same line, meaning that my criteria was that it had to be Rep A and Product A. In the following example, I’m going to add another criteria:

The above criteria says that it will look for Rep A’s Product A sales OR Product B when Total Sales are more than $500.

When I re-run the Advanced Filter, I’ll have to expand my range to include the new criteria:

The results:

The results are either Product A or B, and B only shows up when the Total Sales are more than $500. You can add even more criteria, you’ll just need to expand the criteria range when running the Advanced Filter.

Copying to Another Sheet

In both of these examples I’ve copied data over within the same tab. However, if I wanted to copy it to another sheet, that is possible as well.

To do this, you’ll need to copy the headers you want for the extract into another tab. You need to press the Advanced Filter button on the tab where you want the data extracted to. So if you create another sheet, it’s on that sheet that you’ll need to press the button for the filter to work.

In addition to the headers, you’ll also need to put something in the second row, as the Advanced Filter button will result in an error if there is only one row of data when you click on the button (when you first use it). If there was data there before it should be fine, but the very first time you move data over you might get an error message if there isn’t data on the second row.

Once your headers are properly setup, you run the Advanced Filter button and fill in the same data as before. The one difference you’ll see is that since I’m on a different tab, the sheet name will show up in the Criteria and List range fields:

Generating a Unique List of Values

There are a lot of different ways you can pull a list of the unique values in a list. Excel has even added a button to remove duplicates, but it will remove the other items in your list, so that may not be ideal.

The Advanced Filter will allow you to do this as well, simply by just leaving your criteria blank and checking off the box for Unique records only

If I want to generate a unique list of Salespersons, I’ll run the Advanced Filter with these options:

The result:

Because I only wanted to see the unique values for the Salesperson, I only have that field in my extract list.

Benefit of Using the Advanced Filter

At first glance, you may be wondering why even bother with an Advanced Filter. After all, you can setup the filters yourself using drop downs and then copying and pasting the filtered data somewhere else.

The most likely reason for the Advanced Filter is that in earlier versions of Excel, filtering just wasn’t as strong as it is today.

The main benefit using it today is that it can allow you to easily audit and trace your filters. If you just copy and paste after applying filters you won’t be reminded of which filters you put in place.

If you use older versions of Excel you’ll likely find a lot more usefulness out of the Advanced Filter than if you are using the newer versions. Admittedly, I haven’t had a need to use the Advanced Filter in several years and can’t imagine that changing anytime soon.

Creating a Dynamic Dashboard in Excel

January 27, 2018

by admin with 2 Comments dashboard intermediate pivot chart pivot table

Do you want to create a dashboard that will update all of your charts simultaneously based on what filters your users select? Follow the steps below and you can create a professional-looking dashboard without having to use any complex formulas or programming.

Preparing the Data

If you have data in Excel that you want to use to create a dashboard, there are a couple of things you’ll want to do first to make sure everything goes well.

1. Ensure your data is free of error cells, as this will result in errors.

2. Have proper headings setup so that you know what you are referencing in your dashboard.

3. Setup a named range for your data, ideally a dynamic one. This will make it easier to link everything to your data quickly and easily.

Making sure your data is clean and ready to go is the most important step, but unfortunately the one that is easily overlooked. After all, if you’re data is no good, your outputs won’t be either. Garbage in, garbage out.

You can follow along with my sample data, which can be downloaded here.

Setting up the Pivot Table

First up, let’s look at creating a Pivot Table (see this post for an into into pivot tables).

I’ve assigned a name of Dataset1 to my data, and this is what I will referencing when I create a pivot table. With a named range, I don’t have to worry about selecting the data before clicking the create pivot table button, I can do it from anywhere.

Once I’ve got my pivot table ready to go, the next thing to do is to select my fields. The fields that I have to choose from in my data set include: date, store, salesperson, and product.

For my first pivot table I’ll want to look at the date because I want to start from a high level and work my way down. No sense in starting from the detail when I don’t have any context yet.

For the rows, I’ll select Dates, and in the values I’ll select Total Sales. My table now looks like this:

Ultimately, it doesn’t really matter if you want to select columns or rows for this as it’s going to be in a chart anyway. In Excel 2016, my dates were automatically grouped into months, which is what I wanted. If you want to change the grouping, simply right-click on the dates values and click Group.

Then select the how you want the dates to be grouped

Next, I want to clean up my formatting so that my total sales have commas and so that the data is easier to read. To do this, I’ll right-click on that field and choose Value Field Settings.

Then click on Number Format and then select Accounting.

Creating a Pivot Chart

Now it’s time to convert this into a chart. Select the Insert tab and in this example I’m going to select a regular Column Chart.

What you have now is a simple column chart that shows your sales by month. The only thing special about a pivot chart is that you’re able to filter it based on your pivot table.

You’ll notice there are drop downs on your pivot chart that you select to modify your data. I can select only certain months to look at.

The amount of options you have on here depends on how many fields you added to your pivot table. Whether you make the changes on your pivot chart or pivot table doesn’t matter, the chart will update all the same.

However, for the purposes of a dashboard, I’m going to get rid of these ugly filter buttons on my chart. To do this, click on your chart and click the button for the Field Buttons and this will remove the buttons.

Now that the pivot chart is ready to go, you can now go about and format it how you like.

Formatting and Tidying Up

Once you’re done formatting the chart, move it on to another tab. Because you’re creating a dashboard you probably won’t want your original pivot table to show up along with it. For this reason I usually move all the charts onto a separate tab.

Lastly, you’ll probably want to format your chart so that it is more appealing to your users.

Rinse and Repeat

For a dashboard, you’ll want to create multiple charts and so you’ll likely want to create another pivot chart following the same steps as above. In the next chart you can focus on a more detailed analysis, such as sales by store or rep.

In my example, I added three more charts in total and decided to mix it up by using a column chart, a pie chart, a stacked chart, and a bar chart. Mixing it up a little will keep your dashboard more interesting for your users.

Adding Slicers

Once you’ve added your charts, the next thing you’ll want to do is add slicers. Note that slicers are new to Excel 2010 and if you have an older version you will not be able to utilize these features.

For an overview on slicers, refer to this post.

The real advantage of using slicers is that they can be linked to multiple pivot tables and pivot charts. This allows you to now turn your dashboard into a dynamic one that will update as the user selects options from the slicers.

Once you’ve inserted slicers, you want to make sure that each slicer is connected to every pivot table. To do this, simply right-click on the slicer and select Report Connections.

On the next screen you can see all the pivot tables and charts that the slicer is connected to. Ensure that you have ticked off all the ones you want it connected to and then click on OK.

By doing this your slicer will now update all those charts and tables automatically. Repeat this step for every slicer you create.

Your dashboard is now ready to use and anyone that makes a selection on one of your slicers will see all the charts update immediately.

Using Slicers in Pivot Tables

August 8, 2017

by admin with 2 Comments intermediate pivot table slicers

I am going to use the same pivot table that I left off with in my last post and now I am going to add slicers to show you how they can make your life easier.

***Please note slicers are a new feature in Excel 2010 and you will not be able to use them on older versions***

To insert a slicer is very simple, while having selected a cell on your pivot table, go to the Insert tab and click on Slicer.

Then you will be prompted to select which field you want to use a slicer for:

I am going to select the first four fields: Date, Customer, Store, and Sales Rep. Once you’ve made your selections, click OK. Now you have four different slicers that are on top of your pivot table:

This is of course not ideal, so the first thing you will want to do is move them. You can easily re-size the slicers as you see the current one I have selected for Sales Rep shows circles along the edges that I can use to re-size it.

First what I am going to do is insert a column into column A so that my pivot table pushes over into the next column. I will also insert a few rows above it to make room as well.

I am doing this so it is easier to move over a slicer to the left of the pivot table.

For slicers that have long filters (for example customer names), I prefer to put those above the pivot table, otherwise the names might get cut off. For shorter names, such as the three letter month abbreviations, those can go into the column to the left of the pivot table. Because they are short, they won’t need a big column to accommodate them. However, this is just my preference and you can put slicers wherever you think is most convenient.

I’ve re-arranged my slicers so the months are to the left of the pivot table and everything else is above it:

The slicer to the left of my pivot table looks fine, but the problem is the slicers at the top only show a couple items each, and would require me to scroll to find my selection. You may need to scroll, but there is a lot of empty space that can be used otherwise.

What I want to do is add another column in the slicer. If I select the Customer slicer, under the Options tab under the Slicer Tools section, on the right-hand side there is a field for columns. The default is set to 1, but if I change it to 2 my slicer now looks like this:

This is already a big improvement since now instead of seeing just two items I see four. I can add more columns but in this case without expanding my slicer horizontally it will truncate the names. You can stretch out the slicer as you see fit and adjust the columns accordingly. After adjusted my slicers all to be two columns, my pivot table and slicers now look like this;

If you are finding it hard to line up your slicers so they are even, select the slicer and under Slicer Tools and Options, click the Align button and check off Snap to Grid and Snap to Shape. Doing this will make it easier for your slicer to lock on to other slicers and make it easier to line them up. Do this for each slicer you are having issues lining up. Or you could select all your slicers (using ctrl) and apply the settings to all of them at once.

Now that the slicers are setup they are ready to use. Slicers effectively are filters in a pivot table, but the key difference is their ease of use for any user.

Whatever options I want to filter by I can just click on in the individual slicers. In the below example I am going to select all the sales to Customer ABC and Store 1 for Rep 01. My selections below reflect these selections and now my data table has filtered this data out:

If I want to remove any filters then all I can just click on the filter icon and the red x in the top right corner of the filter. This will reset the selections for that slicer.

Based on my selections I cannot choose any option besides Oct for the Date slicer. This is because based on the criteria I have selected in the other slicers there are no other months that are available. The unavailable selections are indicated by the faded light blue selections.

How to Create a Pivot Table in Excel

July 20, 2017

by admin with No Comment intermediate pivot table

In this post, I’ll show you how to create a pivot table. It’s a key skill in Excel that all users, even beginners, should be familiar with. It can make your analysis a lot easier to do while also presenting your findings in a very easy-to-read format for users of your data.

Why should you use a pivot table?

One of the biggest benefits of using pivot tables is that you can double-click on any total to see the individual items that make it up. This is something that’s not possible with formulas and can sometimes involve a lot of digging. But with pivot tables, the information is only a few clicks away.

Not only is the information easier to drill-down into, but it’s also a lot easier for the person making the report and summarizing the data to create it as well. With formulas, you may have to use many different summation formulas which could get complex very quickly, but a pivot table can take care of all that if your data is organized.

Pivot tables are also good when you’re dealing with lots of data. If you’ve ever used a formula to analyze thousands of rows of data you know that it can start to slow down your spreadsheet, and even your computer. With pivot tables, the data is all stored as a snapshot and a recalculation is only necessary if you add data to it and refresh it. Otherwise, the information will remain there in the background. That saves your spreadsheet the need from always doing calculations in the background.

The one significant downside of pivot tables is that they’re not often as flexible as formulas are it can be difficult to manipulate them to look exactly how you’d like them to.

What this post will cover

In this introductory post I’ll go over a variety of different topics relating to pivot tables, including the following:

How to create a pivot table
How to setup the pivot table’s rows and columns
Grouping dates into months, years, and quarters
Filtering data in a pivot table
Changing the formatting of fields
Changing pivot table values to averages
Show values as a % of a column or row
Adding more fields and changing the view to tabular
Viewing the contents that make up a cell

If you want to follow along with my example you can download my sample file here

1. How to create a pivot table

Actually creating a pivot table is a very simple process. The only requirement is that your columns should have headers and there shouldn’t be gaps in your data to make sure it picks everything up. If your data doesn’t need adjusting, then simply elect any cell on your data table and click on the Insert tab and click on the Pivot Table button (you can also use shortcut ALT+N+V)

That will pop up the following screen:

Excel automatically determines the table range based on the active cell when the create pivot table action was triggered. If the cell wasn’t on the dataset, Excel may not be able to pull this information accurately. As long as you make sure you click the insert pivot table button when you have your data selected then usually Excel will likely get the correct range. Of course, if the information is incorrect you can change the range at this screen as well.

You can select whether you want the pivot table in a new worksheet or the existing one. In most cases, you’ll want a new worksheet, which is what it will default to. Since you can’t overlap data with a pivot table, it’s usually cleaner to just start on a brand new sheet.

2. Setting up the pivot table

When the pivot table is generated you will see the following:

This pivot table is blank and not terribly useful right now. On the right-hand side you will see this:

This is where you will select where you want your data to show. This is going to be the nuts and bolts of your pivot table. If you organize your pivot table well, it’ll display the results that you want. But if you don’t, you could end up with a confusing and useless table.

Suppose for example, that you want to see a summary of sales by store, broken down by month. First, you’d find the Total Sales field from the above list and drag it into the Values section of the pivot table. Immediately, the pivot table will show you the total of all the sales for everything:

However, let’s see this broken down further by store. To do that, move the Store field into the Rows section. Now, you’ll see the totals by store:

Normally you’ll want the field with the most amount of items to go into the rows section. Otherwise, if you put that field into the columns section you will have to do a lot of scrolling to the right to see the entire table, which isn’t ideal; normally you want to be scrolling up and down instead.

Lastly, let’s also move the Date field into the Columns section. This is what the layout will look like now:

Your pivot table should look something like this:

You’ll notice that the grand total is the same from when you dropped in Total Sales and had nothing else. Now, however, the data has just been split by store and by date. Excel has also automatically formatted the dates into months, but that’s not always a guarantee.

3. Grouping Dates by Month, Year, Quarter, etc…

If you want to group your dates or want to change the grouping, select one of the dates on your pivot table and under the PivotTable Tools -> Options (this could be Analyze or PivotTable Analyze, depending on which version of Excel you have) tab, click on Group Field button.

After clicking that button you will see the following options:

Excel has automatically divided the data into days and months. However, you can group this however it makes sense to do so. If you had multiple years, you could split it accordingly. For the sake of breaking down the data even further, let’s also select quarters. Then the pivot table looks as follows:

pivot table showing sales by quarter and by store

This is a good overview of the entire year. However, if you only want a report that looks at a specific quarter, then this might be more than what is needed. Below, I’ll show you how to filter the pivot table to show only certain periods.

4. Filtering data in a pivot table

To filter the data in the pivot table, you’ll need to click on the Column Labels button since the dates are in columns. Then you will see a drop-down option to select the field you want to filter:

Pivot table showing option to select certain periods to filter by.

What you can do now is to select the specific quarter that you’re looking for. If you select just the first quarter, then the pivot table will update accordingly:

Pivot table showing sales sales data for the first quarter only.

You’ll notice now the grand total has been updated to include only the data that has been filtered for, rather than everything in the pivot table.

5. Changing the way fields are formatted in a pivot table

The pivot table looks okay so far except that the way the numbers are formatted is far from optimal. It doesn’t show dollar signs nor is there a comma separating thousands; it’s just not very readable for users at this point. But it can easily be fixed.

While you could just select the entire columns and make changes like you might normally do to ordinary cells but the problem with that is that it’s only a temporary solution; if you refresh the pivot table, the formatting will go back to what it was before. In order to change the formatting for a field permanently, you’ll need to right-click on one of the data points and click on Value Field Settings.

Next, click on the Number Format in the below screen:

Then it’s just a matter of selecting the number format that you want to use. For dollar signs and a comma after the thousands, Accounting format is the best option. Selecting that, the pivot table will now show the following:

6. Changing pivot table values to look at averages

Currently, the pivot table shows the total sales, but it can be adjusted to show the average sales — this will be made up of the average of each individual line item. This can be useful if you want to see the average transaction size per location. To switch to average, right-click on any of the dollar amounts and again go back and select Value Field Settings.

From there, change from Sum to Average. If you click on OK now the table will show averages

You will notice at the top now instead of Sum of Total Sales it now says Average of Total Sales.

7. Show values as a % of a column or row

Rather than averages, now let’s show the values as a percentage of the total month. Again, go back to Value Field Settings and change the calculation back to Sum and then click over on the tab on the right called Show Values As.

In the drop-down selection, select % of Column Total. This will give show the data as a % of the total for the month. Since the dates are in the columns, you would select columns here. After clicking OK, the pivot table looks like this:

Now it’s easy to see the proportion of sales for the month came from each store. Alternatively, you can also see which month made up most of a store’s sales by using % of Row Total rather than the column total. For now, let’s revert back to just showing totals. To do this, just go back to Value Field Settings and under the Show Values As tab select No Calculation.

8. Adding more fields and changing views to tabular

Next up, let’s also add another field, Salesperson, to the rows section which will group the data even further. If you want the pivot table to first be sorted by Store and then Salesperson, the Salesperson field should be dragged under the Store field. If you want the pivot table to first sort by Salesperson and then by Store, then the Salesperson field will need to be above the Store field.

In this example, let’s put the Salesperson field underneath the Store field since it’s might be a more logical hierarchy in this case. After adding the field, you’ll get to a common pivot table problem: it’s in a format that’s just not ideal.

If you want to use the pivot table in a formula or copy it as values somewhere, then this format, Compact, it’s not very helpful since it doesn’t have all the information on one line. Ideally, the store field should show on every line. To do this, go under the PivotTable Tools section again and under Options/Analyze (or the Design tab) and click on Report Layout and select Show in Tabular Form

The pivot table should now look like this:

The layout now has store and salesperson on the same line, but only for the first line. The store field is still showing blank for many of the items.

To fix this, go back to the Report Layout options and this time select Repeat All Item Labels

Here’s the updated pivot table:

This is an easier format to follow since it has all the relevant data on one line and it is easier to read. If you don’t want to see all the stores and the salesperson detail, you can collapse the field by pressing on the – button next to the store name at the top. This will change it to a + and collapse the field. If you want to do this for all stores, right-click on any store and select Expand/Collapse and select Collapse Entire Field.

This will now give you a tidier pivot table:

9. Viewing contents that make up a cell

Lastly, let’s go over how to see the contents of a cell. As mentioned earlier, one of the benefits of a pivot table is that you can simply double click on a cell to see what makes up its amount.

For instance, by double-clicking on the cell in the first row for $3,735, it will open a new sheet with the following data:

What this tells you is that the cell was made up of all of these entries. If you were to double click on the grand total, you would see all of the transactions in the entire pivot table. Once you’re done looking with the tabs, you can modify them how you like or even close them out as they won’t impact the pivot table itself.

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.

How to Setup Conditional Formatting (including Formulas)

June 7, 2017

by admin with 5 Comments conditional formatting formatting intermediate

Conditional formatting is useful for highlighting cells or ranges if a condition is true. For example: highlighting negative values as red or positive ones as green. You can also do more complex formatting like highlighting an entire row if it meets a criteria.

Creating A New Rule

To get started with conditional formatting, you need to select new rule from the conditional formatting options which is under the Home tab:

You will then have quite a few options as to what you want to do:

conditional formatting all cells based on values

Format All Cells Based On Their Values

This is useful if you want to show some sort of progression from one value to the next, as one colour will fade into the next. Here is an example using this conditional formatting on a range of values from 1 to 5:

1 started from the dark orange and gradually got to a light yellow colour by the time it got to the number 5. You can change the colours involved or even the range. You also can change the values instead of using lowest to highest values you can hardcode figures, use percentages, or whatever else. In all likelihood it will show something very similar regardless what you select, so leaving it to the default setting here (low to max) is going to be sufficient in most situations.

Another way you could use this formatting is if you wanted to compare a time-series. Assume the below are sales numbers and you wanted to highlight good and bad years.

You can see in this example there is no longer the smooth transition as in the prior example since I’ve assumed sales are bouncing up and down. The downside of using this type of conditional formatting as you could have a really colourful dataset if you did this.

You may like it initially but if you are dealing with lots of data it may not be all that helpful because you are dealing with many different shades of colours now and you may find yourself comparing different shades to see if one is darker than the next. And conditional formatting is most useful when you don’t have to spend time analyzing colours, and instead the colours help you do the analysis by easily standing out and highlighting what you want to see.

Format Only Cells That Contain

The next option on the formatting rules allows you to specifically look at the cell values. Keeping my sales numbers example from above I want to highlight cell values from 200 to 500. In this case I only need to select between and a low value of 200, and a high of 500.

Now unlike the format all cells example, the remaining conditional formatting rules require you to explicitly set what formatting you want to apply, it won’t just smooth colours from one to the other. And every cell in the range won’t have conditional formatting on it unless I explicitly state it. To set the formatting for I want cells that fall in this range to be I just click on the Format button below and apply what formatting I want. In this example I just set the cells to be highlighted in green.

(Note you do not need to put the = sign before the number, it automatically does this after you have already setup the conditional formatting.)

Below is my result

The cells that do not fall within this range do not have highlighting. If I wanted them to have highlighting I would have to change the rule, or add another rule for them. So what I will do is add another rule for any values under 200:

In this case I set the formatting to be light red.

Please note you still want to make sure your range is selected when you are adding a new rule so that it gets correctly applied to the range. Otherwise you will need to adjust the conditional formatting settings so that they are applied to the correct range.

Both the drop downs that currently contain Cell Value and less then can be changed. Cell Value can be changed to the following:

If you change this value then some of the options for the next drop down will change as well. Obviously you cannot choose less then or greater than operators when dealing with text. There are a lot of possibilities here so you can experiment with them by changing these drop down selections. Currently, for the Cell Value selection, these are the different operators available:

Going back to my example here, I selected the less than operator since I wanted to highlight values that were under 200. My result is the following output:

conditional formatting between less than

Now I have formatting for every number except 800. So I could make a similar rule and set that one to anything over 500.

The disadvantage of using the formatting based on values in the first example is that not all your values have conditional formatting on them. But this could be an advantage as well as it allows you to have more control over the exact type of formatting you want. Here I can have green and red which helps to quickly distinguish the results without having to closely look at the shading.

Format Only Top Or Bottom Ranked Values

This formatting option allows you to just highlight your top or bottom values.

conditional formatting top ranked values

I have selected the top 10 to be highlighted in green and as a result I get the following:

The problem here is I don’t have more than 10 values so everything will be highlighted. In fact, even if I were to select bottom 10 then that would apply to everything as well.

One way around this is to check the box for % of the selected range. now it will look at the top 10% rather than just the top 10 values.

Now my highlighting looks as follows:

What this effectively does now is look at the percentiles and pulls the top 10%. So if I had a data set of 20 values, it would highlight the highest two values.

Format Only Values That Are Above Or Below Average

This option will just compare the value against the average.

conditional formatting above below average

If I select that it highlights anything above the average I will get the following result:

The average for this data set is 330, so it correctly has highlighted the values 500 and 800.

Format Only Unique Or Duplicate Values

This is probably the simplest formatting option where you have only one of two options – unique or duplicate.

In my example all of my values would be highlighted since none are duplicates.

Use A Formula To Determine Which Cells To Format

This is the most versatile option for formatting. But also is the one that will take the most time to setup. In an earlier post I showed how to use this option to create highlighting on alternate rows.

In this example I’ve downloaded Alphabet’s financials from Google Finance for the past five quarters. This is what it currently looks like:

I will start with setting up a rule to highlight every column where income after tax is more than 5,000. To do, I will select cell B11 and setup the following rule:

I have frozen row 11 since that is where the after tax numbers are, and I want these figures to change based on what column I am in, but not what row I am in. So for that reason I am freezing the row and not the column.

I selected cell B11 when entering this formula because I wanted to be in the same column because when I go to re-size the cells that I want this formatting to apply it to, it will adjust the formula. So if I was in column C and entered my formula as above (referencing column B) then if I change the range I want to apply it to, say columns B:F rather than just the cell I was in in column C, the cell it will be evaluating now will be A11, rather than B11. It will reflect the fact that my range has changed (unless of course I wanted to freeze the column as well but that would not be helpful in this situation).

Which brings me to the next step: applying this to the relevant columns. Initially when you setup rules for conditional formatting it by default assumes you are applying them to the range you have selected. So I could have selected the range B:F but I can just go back into my conditional formatting rules and change the range:

Now this rule will apply to all the columns from B to F. As a result, my updated data set looks as follows:

Now every column where Income After Tax was more than 5,000 has been highlighted.

The easiest way to understand formulas in conditional formatting is this: treat it as an IF function, except start from the logical test argument and ignore the values that they will be if they are true or not. After all, if it is true, the formatting applies, if it is false, it will not apply. In the above example my IF function would have been something along the lines of this:

=IF(B$11>5000,X,Y)

Where X is the conditioning applies, and Y is that it doesn’t.

I will apply this logic to use a length (LEN) function. For no logical reason whatsoever, I am going to highlight all the rows that have descriptions in column A that are both longer than 20 characters and have a comma in them. If I were going to use an IF function, the formula would look as follows (in cell B1):

=IF(AND(LEN($A1)>20,ISNUMBER(FIND(“,”,$A1,1))),X,Y)

If I were to apply it to conditional formatting it will look as follows:

As you can see it’s a copy and paste from my IF function, just the logical test argument. I enter this in cell B1 just to make sure the referencing doesn’t change and then I apply it to columns B:F and my data set now looks as follows:

Managing Multiple Formatting Rules

I have an overlap now in rows 3 and 5 as they are highlighting the areas that were previously highlighted in green. If I wanted to change this to make those back to highlight in green I can change the hierarchy of my formatting rules. I can change this by going into Conditional Formatting -> Manage Rules.

If you do not see any rules even though you have set them up, make sure at the top where it says Show formatting rules for that This Worksheet is selected. See below:

If the range selected above is not correct then you may not see your conditional formatting rules.

My rules look as follows:

I can change the hierarchy by selecting the green highlighting criteria and clicking on the up arrow to move it above the yellow highlighting criteria. That will mean the green highlighting rules will be applied first. That still won’t keep it green since it just means the yellow highlighting criteria will be applied afterward. This is also the screen where you can delete any formatting you no longer want.

Instead, what needs to happen is for the Stop If True field to be ticked off for the green highlighting rules:

conditional formatting rules manager stop if true

Now the green highlighting is first and if the condition is met the yellow highlighting rules will not run. Now my data set looks as follows:

The yellow highlighting rules have now only been applied to the columns where the green highlighting did was not. By using the Stop If True and setting your hierarchy for formatting you can prioritize what formatting you want to be applied.

Table containing numbers specifying if they are even or not.

How to Count the Number of Cells With Text in Excel

April 7, 2017

by admin with No Comment arrays count countif intermediate len

Counting blank and non-blank cells is fairly straightforward, but what about the cells that have formulas in them that don’t return a result and look blank? They can distort those calculations. In this post, I’ll cover how to count the number of cells with text in an Excel spreadsheet (regardless of if they contain formulas or not), using multiple approaches.

I’ll use the table below for the basis of my calculations which includes some values that look empty (even though they aren’t).

In column A I have the numbers from 1 to 11 listed. In column B I have a formula to determine if the number in column A is even, and if it is, I will place the word EVEN as my result, otherwise, it will be blank. The formula I used is the MOD function, which tells me how many remainders there are after dividing by a number. It has two arguments: the number I want to divide, and by what factor. My formula in cell B2 looks as follows:

=IF(MOD(A2,2)=0,”EVEN”,””)

In the above example, I am dividing cell A2 (1) by 2 and saying if it equals 0 (suggesting no remainder), then I want the result to return the word EVEN, otherwise, I want the cell to be blank (“”). Since 1 divided by 2 does have a remainder, the result in column B is a blank value (“”). In the next row, since the number 2 does not have a remainder, the result in column B is “EVEN.”

All the cells from B2:B12 have formulas, although some look empty.

Using COUNT, COUNTA, COUNTIF Functions

The conventional way to count non-empty cells is using the COUNTA function. A:

>=COUNTA(B2:B12)

The above formula will return a result of 11, since all 11 cells in the range are not empty, which is correct. But this doesn’t tell me how many actually contain values. If I wanted to count how many cells had numbers, I would use the COUNT function. This won’t count text, however.

=COUNT(B2:B12)

The above formula yields a result of 0, since there are no numbers in that range, otherwise, it would have worked fine. One workaround I could do is the COUNTIF function. I can count the number of blanks(“”) in the range:

=COUNTIF(B2:B12,””)

This returns a result of 6. I could combine the COUNTIF and COUNTA functions to arrive at my answer as to the number of cells that contain values that aren’t formulas:

=COUNTA(B2:B12)-COUNTIF(B2:B12,””)

This will result in 11-6 = 5. In Excel, there is usually not one way to solve a problem, so I’ll show you another way to accomplish this.

Using An Array Formula

The great thing about array formulas is they allow you to do multiple things in one formula that you couldn’t otherwise do with regular formulas (at least, not in one step). I am going to use the LEN function which tells me the length of a cell. If a cell is empty, it will return 0. If there is even one letter or digit, LEN will equal 1. I want to evaluate every cell’s length, and then tally all those that have a length of at least 1.

The LEN function would look as follows:

=LEN(B2)

This will result in a value of 0, since cell B2 has nothing in there (even though a formula exists). It is a simple function with only one argument as you can see. I will go a bit further and combine it with an IF function to return a value of 1 if there is something in the cell, and a value of 0 if there is not.

=IF(LEN(B2)>0,1,0)

The last step is to use the SUM function to now total all these values. If the non-empty cells return values of 1, then I just need to sum them all of them to get my count. The formula (before turning into an array) looks like this:

=SUM(IF(LEN(B2:B11)>0,1,0))

All I have added is the SUM function before my IF function, as well as an additional closing bracket. To turn it into an array formula, when editing the cell I have to click CTRL+SHIFT+ENTER and my formula will now look as follows (on newer versions of Excel you don’t need to do this):

{=SUM(IF(LEN(B2:B12)>0,1,0))}

This will return a result of 5 which correctly returns the same result as when I combined the COUNTA and COUNTIF functions. Below is a summary of the results:

Excel spreadsheet showing a variety of count formulas.

If you liked this post on How to Count the Number of Cells With Text 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.

Use Excel’s Random Number Generators to Populate Sample Data

March 16, 2017

by admin with No Comment index intermediate rand randbetween random

Excel has random number generator functions which are useful if you need to test a template or create some sample / dummy data.

The RAND function returns a number between 0 and 1. You could multiply this by a factor of 10 or 100 to get a much larger number if you need it. Once you are happy with the data that has been populated then you will want to copy and paste it as values otherwise the numbers will change every time a recalculation occurs. This is true of any random number function in Excel.

Similarly, the RANDBETWEEN function will return a random number between a range that you specify. In the below example I use 1 and 100:

If I used the RAND function and just multiplied by 100 I could get similar results to RANDBETWEEN. The latter just saves you that extra step by being able to specify your parameters right in the formula. It allows you also to be more specific (say for example I wanted a random number between 1 and 35, the RANDBETWEEN function would certainly be easier to use)

If you do not like the random numbers you have generated, you can simply just hit the delete key on an empty cell and your random numbers will be regenerated. If you don’t want your numbers to change anymore then you will want to copy them and paste as values.

To use this to create sample data I am going to make two lists, one for employees, one for stores. From there, I can use the RANDBETWEEN function in conjunction with the INDEX function to extract values from the lists:

In columns A and B I am just using the RANDBETWEEN function to select a number between 1 and 5, as that is the number of different employees and stores I have listed in columns C and D. In columns E and F I use the INDEX function to extract from those lists using the random numbers generated in columns A and B.

I will break down the INDEX formula in column E a little bit here:

=INDEX($C$2:$C$6

In the first argument I am selecting my employee list since this is where I want the result to come from. I also lock the cells using the $ sign to ensure that as I copy the formula down that range is locked and will always reference C2:C6.

=INDEX($C$2:$C$6,A3

The second argument in the INDEX function is the row number from where I want to pull my value. Cell A3 is my first random value – which in this case is 4. This means that from the Employee List range (C2:C6) I want the value on the fourth row of that range – which is not row 4, it is row 5 since I start counting from the start of the range, which is on row 2. As I copy this formula down the row number will change to the corresponding value in column A.

Because the lists only have one column you could actually stop here.

=INDEX($C$2:$C$6,A3, 1)

The last argument in the INDEX function is the column number. In this example I only have 1 column in my lists so the value is equal to 1. You could skip this argument and it will still work however it’s good habit to always enter the column number.

The above formula tells me to look at range C2:C6, and extract the value from the row that is referenced on A3 (which is 4), and from column 1. That point of intersection is Employee 4, since it is on the fourth line of that range, and in the first and only column.

If I copy the formula down a cell it will do the same except this time pull the value from the row referenced in cell A4 – this time it is 2. As a result, my result is Employee 2 since that value is on the second row of the range and again in the first column.

Column F is the same formula as column E except this time it references the store list (column D) and the second RANDBETWEEN column (column B). This is just to show you how you might fill in multiple items. You wouldn’t want to use column A again otherwise you are guaranteed that the same row will be returned and you will always have Employee 4 tied to Store 4, and thus, not truly random combinations.

In this example I have my lists, random numbers, and results all in the same area for illustrative purposes but they do not need to be even on the same sheet.

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.