Yes. In Excel for Microsoft 365 on Windows, use a VBA Scripting.Dictionary to group records by category, write the resulting category-and-total table to a worksheet, and create a chart from that table. A dictionary organizes the data; Excel’s chart object draws the plot, so the chart’s source should be a worksheet range rather than the dictionary itself.
What a dictionary contributes to a chart
A dictionary stores pairs of keys and values. For a plot, a key can be a category such as a product, region, or status, while its value can be a count or a running total. If a source list has many rows with repeated category names, the macro can use each name as a key and add the corresponding numbers together. The result is a compact summary table that Excel can chart.
This is useful when a chart needs to be rebuilt from changing or repeated records. For example, a sales list might contain several rows for each product. The macro can total the sales per product and produce one column per product, rather than plotting every transaction separately. A dictionary is not required for every chart: a PivotTable, worksheet formulas, or a chart built directly from a clean table may be simpler for routine reporting.
Prepare the source and summary sheets
The example below assumes a worksheet named Data with category names in column A and numeric values in column B. Row 1 contains headings; records begin in row 2. It writes the grouped results to a worksheet named Summary, using columns A and B for category and total. Create both sheets and give the columns headings before running the macro. Use a separate summary sheet because the macro clears its contents before writing fresh results.
- Data!A1: Category; Data!B1: Amount.
- Data!A2:A…: category names; Data!B2:B…: numeric amounts.
- Summary!A1:B…: reserved for the macro’s output.
In this version, blank category cells and rows with nonnumeric amounts are skipped. If your data uses a different layout, change the worksheet names and column references in the code. The code treats category names without regard to letter case, so “North” and “north” are combined; it trims spaces at the start and end of names, too.
VBA macro to group values and make a plot
In Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro into the module. If you use a keyboard that requires it, press Fn with Alt+F11. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code.
Option Explicit
Sub MakeCategoryPlot()
' Group amounts by category, then chart the summary table.
Dim wsData As Worksheet
Dim wsSummary As Worksheet
Dim totals As Object
Dim lastRow As Long
Dim rowNum As Long
Dim outputRow As Long
Dim category As String
Dim amount As Variant
Dim key As Variant
Dim chartObject As ChartObject
Dim sourceRange As Range
Set wsData = ThisWorkbook.Worksheets("Data")
Set wsSummary = ThisWorkbook.Worksheets("Summary")
Set totals = CreateObject("Scripting.Dictionary")
totals.CompareMode = vbTextCompare
lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
For rowNum = 2 To lastRow
category = Trim$(CStr(wsData.Cells(rowNum, 1).Value))
amount = wsData.Cells(rowNum, 2).Value
If Len(category) > 0 And IsNumeric(amount) Then
If totals.Exists(category) Then
totals(category) = CDbl(totals(category)) + CDbl(amount)
Else
totals.Add category, CDbl(amount)
End If
End If
Next rowNum
If totals.Count = 0 Then
MsgBox "No category rows with numeric amounts were found.", vbInformation
Exit Sub
End If
wsSummary.Cells.ClearContents
wsSummary.Cells(1, 1).Value = "Category"
wsSummary.Cells(1, 2).Value = "Total"
outputRow = 2
For Each key In totals.Keys
wsSummary.Cells(outputRow, 1).Value = key
wsSummary.Cells(outputRow, 2).Value = totals(key)
outputRow = outputRow + 1
Next key
Set sourceRange = wsSummary.Range("A1:B" & outputRow - 1)
On Error Resume Next
wsSummary.ChartObjects("CategoryPlot").Delete
On Error GoTo 0
Set chartObject = wsSummary.ChartObjects.Add( _
Left:=300, Top:=20, Width:=520, Height:=320)
chartObject.Name = "CategoryPlot"
With chartObject.Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=sourceRange
.HasTitle = True
.ChartTitle.Text = "Total by Category"
.HasLegend = False
End With
End Sub
To run it, return to Excel, press Alt+F8, select MakeCategoryPlot, and choose Run. The macro replaces the contents of the Summary sheet and deletes a chart named CategoryPlot there before adding a new one. Check that the summary sheet contains no information you need to preserve before running it.
How the macro turns records into a chart
The loop reads each source row and uses the trimmed category as a dictionary key. When that key already exists, the macro adds the current amount to its stored total; otherwise, it creates a new key. For example, rows for “Pens” with amounts 12, 8, and 5 become one summary row for Pens with a total of 25. The chart then uses the summary headings and values as its source.
The dictionary is created with CreateObject, which is called late binding: the module does not need a manually selected reference to the scripting library. The macro writes its results to cells because Excel’s chart object reads worksheet data. The dictionary itself is temporary; it exists only while the procedure runs. The chart remains on the Summary sheet after the macro finishes.
The code sets CompareMode before adding keys. That setting makes category matching case-insensitive. It does not remove punctuation or combine genuinely different labels, such as “North East” and “Northeast.” Trimming removes outside spaces, but it does not standardize spelling. Clean inconsistent labels in the source data or add explicit normalization rules if those distinctions should be treated as one category.
Choose a chart type and ordering
The sample uses xlColumnClustered, which is a vertical column chart suited to comparing totals across categories. To make a horizontal bar chart, change the chart type to xlBarClustered. A line chart is usually more appropriate when the horizontal axis represents a meaningful sequence, such as dates; categories without a natural order can make a line imply a trend that the data does not contain.
Dictionary keys are written in the order returned by the dictionary, and the code does not sort them. If a particular order matters, sort the summary table before creating the chart or add sorting logic. For a chronological chart, store real Excel dates and arrange the summary rows by date. For a ranked comparison, sort totals from largest to smallest so the chart is easier to scan.
The example gives the chart a title and hides its legend because it contains only one series. You can change the title text in the code. If you later chart multiple measures, keep a legend and arrange the summary table with one category column and a separate column for each series.
Common problems and how to fix them
- “Subscript out of range”: A worksheet name in the code does not exactly match a tab name, or the required sheet is missing. Rename the tabs or update the two worksheet references.
- No chart or an empty chart: Confirm that category names are in column A, numeric amounts are in column B, and data starts on row 2. Text such as a currency symbol stored as part of a value may cause
IsNumericto reject it. - Some rows are ignored: Blank categories and nonnumeric amounts are deliberately skipped. Check for formulas returning empty text, numbers stored as text, or unexpected content in the relevant columns.
- Unexpectedly combined categories: The dictionary ignores case differences and trims outside spaces. It does not distinguish “Oak” from “oak,” but it does distinguish “Oak Street” from “Oak St.”
- Existing summary information disappears:
Cells.ClearContentsclears values and formulas on the Summary sheet. Reserve that sheet for output, or change the clearing line to target only a specific range.
If amounts contain errors such as #N/A, IsNumeric will not accept them as valid totals. Inspect and correct the source rather than silently treating errors as zero unless zero is genuinely the intended meaning. For a large dataset, this cell-by-cell version is easy to understand but may be slower than reading a range into an array and processing it in memory.
Dictionary versus PivotTable or worksheet formulas
Choose a dictionary when a macro needs to aggregate values as part of a repeatable automation task, especially when later VBA steps will also use the grouped results. It gives the code direct access to each key and total. It is a good fit for a one-button process that refreshes a summary and chart, provided the input structure is predictable.
Choose a PivotTable when users need to explore the data, change groupings, filter records, or refresh a report without editing VBA. PivotTables are designed for interactive summarization, although chart formatting and refresh behavior still need to be managed. Worksheet formulas can be more transparent for a small, formula-driven report. In Microsoft 365, functions such as UNIQUE and SUMIF can help build a dynamic summary, but formula availability and the desired chart behavior should be considered.
None of these options makes a dictionary plot directly. All ultimately provide a chart with a range of category labels and values. The main choice is how to create and maintain that range, not whether Excel can chart a VBA object.
Windows, Mac, and macro safety
This code targets Excel for Microsoft 365 on Windows and uses the Windows Scripting Dictionary through CreateObject("Scripting.Dictionary"). Excel for the web cannot run VBA macros. On Mac, VBA is available in desktop Excel, but this Windows scripting object is not a dependable cross-platform choice; ActiveX and Windows-only automation components are not supported there. For a Mac-compatible macro, replace the dictionary approach with a VBA Collection or arrays, or use a PivotTable or worksheet-based summary. The grouping logic needs adjustment rather than assuming the Windows code will run unchanged.
Macro security also matters. Run code only in workbooks you trust, and enable macros only when you understand their source and effect. This procedure changes the Summary sheet and adds or replaces a chart; it does not alter the Data sheet. Keeping a backup is still sensible, particularly before adapting the code to clear other ranges or modify source records.
Frequently Asked Questions
Can I plot a VBA dictionary directly?
No. Excel charts use worksheet ranges or chart series as their data source, not a Scripting.Dictionary object. Write the dictionary keys and values to cells first, then set the chart’s source range, as the example does.
Can the macro count categories instead of summing amounts?
Yes. Store a count for each key and increase it by one for every valid row instead of adding column B. The output table would then contain one row per category and a frequency column, which can be charted in the same way.
Why does the chart order change?
The macro does not specify a sort order. If order matters, sort the summary rows before setting the chart source range. For a stable alphabetical or largest-to-smallest display, add sorting after the dictionary has been written.
Will this work in Excel for the web?
No. Excel for the web can display a workbook containing VBA, but it cannot run the macro. Use desktop Excel for Windows to run this particular code, or create the summary and chart with worksheet features instead.
What if my categories are in different columns?
Update the column numbers used to read the category and amount, and update the last-row calculation if the category column changes. Keep the header row and first data row assumptions consistent, or adjust the loop’s starting row.
Related
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Send Email From a Shared Mailbox in OutlookYes. In classic Outlook for Windows, VBA can create a message and set its SentOnBehalfOfName property to the…
- VBA Can’t Find Project or Library? How to Fix ItThe VBA error “Can’t find project or library” usually means the workbook’s VBA project has a broken reference…
- Excel VBA UserForm Cancel Button: Setup and BehaviorTo make an Excel VBA UserForm button act as Cancel, set that CommandButton’s Cancel property to True, then…
- Microsoft Excel VBA Dictionary: How to Use ItA Microsoft Excel VBA Dictionary is a key-and-value collection that lets a macro look up, count, and update…
- Excel UserForm TextBox Date Format: dd/mm/yyyyTo display a date as dd/mm/yyyy in an Excel VBA UserForm TextBox, validate the text and format it…
- Excel Macro Keyboard Shortcuts: Assign and Run MacrosIn Excel for Microsoft 365 on Windows, assign a macro shortcut by choosing Developer > Macros, selecting the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.