Showing posts with label Combo. Show all posts
Showing posts with label Combo. Show all posts

Wednesday, February 20, 2013

Need to combine two chart types? Create a combo chart and add a second axis

AppId is over the quota
AppId is over the quota
This post is brought to you by Robin Wakefield, a Program Manager in the Excel Team.

Have you ever had two different types of data that you wanted to show in one chart?  For example you may want to plot actual revenue with forecasted revenue to compare the two values.

In an earlier post, we showed you how to create combination charts in Excel 2010, but we have revamped this experience as a part of Excel 2013 to make it much easier to create combo charts. I am going to walk through the same scenario used in that article so you can see the differences.

Suppose I work at a manufacturing company, and I'd like to analyze the number of units we've sold over the last few months and the total revenue generated.   I'm hoping to identify trouble spots, such as high unit sales that result in low revenue, which may in turn indicate that the units are being sold too cheaply.  I would like to create a single chart like the one below so I can see both data sets in one view.

01 Combo Chart - Units Sold and Total Transactions

To follow along, use this sample workbook.

1. Select the data you would like to use for your chart.

2. Go to the Insert tab and click Recommended Charts.

3. Click the All Charts tab and select the Combo category.

At the top of the dialog you will see a couple pre-canned combo charts to get you started and Clustered Column - Line is the default.  This combo chart will split the series 50/50 between a clustered column and a line chart. You can then use the table below the chart to change each series to a particular chart type and move series to the secondary axis.

02 Combo Chart - Insert Chart

Given that units sold and total transactions have different scales we need to move one of them to a secondary axis so we can clearly see the values for both.

4. Check the Secondary Axis box for the Total Transactions and click OK.

Your chart should now look like this:

03 Combo Chart - Initial Output

As you can see it now takes four steps to create a simple combination chart instead of the eleven steps outlined in the earlier post!

Now we can walk through the finishing touches to make your combo chart look more professional. 

There are a couple steps in the previous post that are no longer necessary since they are the new defaults for 2013:

Legend is already on the bottom.Chart title is already added to the chart and all you need to do is edit the text.So the two steps left are to add axis titles and format the Total Transactions axis as $.

1. Click the Add Chart Elements button next to the chart.

04 Combo Chart - Add Chart Elements Button

2. Check Axis Titles.

3. Edit the axis title text

1. Right click on the secondary axis (Total Transactions) and choose Format Axis.

05 Combo Chart - Format Axis Drop Down

2. Expand the Number category.

06 Combo Chart - Expand the Number Category

3. Set the Category to Currency and the Decimal Places to zero.

07 Combo Chart - Set Number to Currency and Decimal Point

And now your chart is completed.   

08 Combo Chart - Final Output

I think you will find that creating combo charts and modifying them to get to your final polished output is much easier now. Let us know what you think in the comments below!

--Robin Wakefield,  Excel Program Manager


View the original article here

Saturday, June 9, 2012

Using a Combo Box to search as you type

AppId is over the quota
AppId is over the quota

(Guest blogger, Molly Pell is a Senior Systems Analyst at FMS, Inc., a leading Access applications company, which designs custom solutions to improve operations for small and large organizations in all sectors.)

Here's a neat trick that you can use to filter a Continuous or Split form while your users are typing in a Combo Box. As the user types, the form filter updates to display full or partial matches for the value entered. 

This example uses a slightly modified version of the Northwind 2007 Template, available for download from Microsoft. The "Customer List" form is a Split form that displays a list of all customers. We want to allow users to filter this list to easily find a customer, even without knowing the full customer name.

To do this, we add a Combo Box named cboFilter, with the following notable properties:

 

Here's what our form looks like:

Then in the Change event of the Combo Box, we add the following code:

Private Sub cboFilter_Change()

  ' If the combo box is cleared, clear the form filter.
  If Nz(Me.cboFilter.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False
  ' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
ElseIf Me.cboFilter.ListIndex <> -1 Then
Me.Form.Filter = "[Company] = '" & _
Replace(Me.cboFilter.Text, "'", "''") & "'"
Me.FilterOn = True
  ' If a partial value is typed, filter for a partial company name match.
Else
Me.Form.Filter = "[Company] Like '*" & _
Replace(Me.cboFilter.Text, "'", "''") & "*'"
Me.FilterOn = True

  End If
  ' Move the cursor to the end of the combo box.
Me.cboFilter.SetFocus
Me.cboFilter.SelStart = Len(Me.cboFilter.Text)

End Sub

Now as the user types, the customer list automatically updates. If the user types an exact match, the list is filtered for an exact match. If the user types a partial match, the list is filtered for any company whose name contains the string. If the user clears the combo box, the filter is cleared.

Note that this same thing can be done with a text box, but using a combo box adds the extra perk of making all values available in the drop down.

For more combo box tips, check out our Top 5 Combo Box Tips for Access at FMS, Inc. 


View the original article here