Posts

Excel provides several ways insert a pivot table and pivot chart in one step. First, try Recommended Charts. This icon represents a pivot chart. In that case, use the Pivot Chart button on the Insert tab of the ribbon. When you click OK, you’ll get a new blank pivot table and blank pivot chart. From there, you’ve got the option to add fields to either to build the chart. Recommended charts will show you previews of chart options, although strangely the pivot table icon is missing....

Let’s take a look. Referencing cells on other sheets is very similar toreferencing cells on the same sheet. Start with an equal sign, and then move to the sheet you want to reference. In this case, we’re just going to build a simple link to the scores in Week1. Sheet names always appear with an exclamation point at the end, before the cell reference. To complete the formula, there’s no need to go back to the original sheet....

Lets take a look. Here we have a simple pivot table and pivot chart already set up. As always, any change to the pivot table is reflected in the pivot chart, and vice versa. Lets say we want to add a second chart using the same data. We can easily copy and paste the existing chart to another location to create another copy. This is because both charts are linked to the same pivot table....

In this lesson we’ll look at how to move charts from one state to the other. Let’s take a look. To move a chart into its own sheet, first, opt for chart. Then, go to the Design tab under Chart Tools and hit the Move Chart button. You could also give the chart a different name at this point. The new sheet will be to the left of the sheet you started on....

A scatter plot or scatter chart is a chart used to show the relationship between two quantitative variables. On this worksheet, we have the height and weight for 10 high school football players. Let’s create a scatter plot to show how height and weight are related. When I hit the mouse, Excel builds the chart. Let’s check Select Data to see how the chart is set up. Here you might see there is one data series....

Here we have some data that is a good candidate for a table. Each row represents an entry or record with information that belongs together. Each column has a unique name. The first step in creating a table is to remove any blank rows or columns. Tables are designed to manage data in one contiguous block of cells. Next, see to it column names are unique. Excel will display the Create Table dialog....

When Excel prompts you for a name, punch in a suitable file name and save the template. Notice Excel will add the chart template extension automatically, “crtx”. To apply a template to an existing chart, right hit the chart and choose Change Chart punch in. In the window that opens, opt for Templates folder. Then choose a template and click OK. The template will then be applied to the chart....

Let’s take a look. you’re free to easily do this with Excel’s Date functions. First, enter the start date. Next, add a formula that begins with theDATE function. For each argument, use the corresponding function to extract the value you need from the start date. Now when I copy that formula down, the dates change by one month. Notice that Excel takes care of changing the year value for me....

This is sometimes called “zebra striping”. In this spreadsheet, we have a table of employees with a small amount of formatting. This is a good option, especially if you want the table for other reasons as well. However, you might also apply dynamic zebra striping with conditional formatting and a formula. The classic formula for zebra striping uses the MOD function. MOD takes a number and a divisor, then returns the remainder....

We already customized the value axis, so let’s make some changes to the horizontal category axis. First, let me point out that axis options are different depending on which axis throw in is selected. With the vertical axis selected, we see value axis prefs. When I go for the horizontal axis, we see category axis prefs. Notice bounds have been set automatically based on the 5-year date range, and units are set to years....

Here we have historical data showing average 30 year mortgage rates over a 5 year period. Let’s walk through some of the options for customizing the vertical value axis. To start off, right-click and select Format axis. check that you’re on the axis options icon. prefs are grouped in 4 areas: Axis options, Tick marks, Labels, and Number. Since these are interest rates all over 3%, I’ll set the minimum to 3....

Here we have a simple set of generic shipping data. We have a date, quantity, and a field to indicate batch number. The first thing you probably notice is that there are some gaps in the columns. So, the first thing I’ll do is set the axis punch in to text. The dates still appear, but now they’re plotted at equal intervals. Now let’s customize the actual labels. Instead you’ll need to open up the Select Data window....

In this lesson, we’ll look at how to clear or delete conditional formatting rules. Let’s take a look. Before you clear conditional formats, you might want to verify where they are currently applied. Choose Find & Select, then Conditional Formatting. Excel will then select all cells that have conditional formatting applied in the current worksheet. To remove an individual rule, just pick the rule and pick the Delete Rule button....