Gold Data in Excel: Build a PivotTable

How-to basics · Updated November 21, 2026

Yes. To analyze gold data in an Excel PivotTable, put each observation in a separate row, keep dates and numeric values in the right data types, then select the range and choose Insert > PivotTable. A PivotTable can summarize a gold-price dataset by date, instrument, currency, or other fields; it does not supply the gold data itself.

What counts as gold data?

Gold data can refer to several different measures, and they are not interchangeable. A table might contain spot prices, futures prices, prices from a particular exchange, or the market price of a gold-related fund. It might record one observation per day, hourly observations, or transactions. Before building a summary, identify what each row represents and what a price means.

For example, a daily spot-price file could have one row per date and columns for the date, price, currency, and unit of measurement. A futures file may also include a contract month. A transaction file could instead have one row per purchase or sale, with fields such as transaction date, quantity, and transaction price. Those datasets answer different questions: daily observations can show how a quoted price changed, while transaction records can summarize activity in the records you have.

Pay attention to the unit and currency. A value expressed in US dollars per troy ounce cannot be compared directly with a value in euros per gram without conversion. The price of a fund share is also not the same thing as a quoted spot price for gold. Include fields that identify the source, instrument, currency, unit, and time interval whenever they are relevant.

Prepare the source data

A PivotTable works best with a simple rectangular dataset: one header row, one record per row, and one kind of information per column. Avoid merged cells, blank header cells, subtotal rows, and decorative title rows inside the data range. Those additions can make Excel misread the source or create confusing fields.

A practical daily dataset might use headers such as Date, Instrument, Source, Currency, Unit, and Price. If the source provides open, high, low, and close values, keep them in separate columns rather than combining them into one cell. If it supplies multiple observations on a date, retain a time or observation identifier so that individual records remain distinguishable.

  • Use consistent labels. Do not alternate between labels such as Spot, Spot Gold, and Gold Spot if they mean the same category.
  • Store dates as real Excel dates, not text that only looks like a date. A date that Excel recognizes can be sorted and grouped chronologically.
  • Store prices and quantities as numbers. Remove currency symbols or thousands separators only if they prevent Excel from recognizing the values, and preserve the currency in a separate field.
  • Keep missing values genuinely blank or document a clear missing-data convention. Do not enter zero for a missing price: zero is a number and will affect averages and other summaries.
  • Keep source notes and units in the workbook, perhaps on a separate notes sheet, so a future reader knows what was collected.

To turn the source range into an Excel Table, click a cell in the data and choose Insert > Table, confirm that the range is correct, and make sure My table has headers is selected. A Table is useful because it expands when new rows are added and gives the PivotTable a named, structured source. On Windows, Ctrl+T also opens the Create Table dialog. On a Mac, use Command+T. The exact dialog appearance can vary slightly by platform.

Create a PivotTable in Excel for Microsoft 365

In Excel for Microsoft 365 on Windows, click a cell in the Table or source range, then choose Insert > PivotTable. Check the proposed Table/Range or table name, choose where to place the PivotTable, and select OK. For a first analysis, New Worksheet is usually the clearest option. Excel creates an empty PivotTable and displays the PivotTable Fields pane.

  1. In the PivotTable Fields pane, identify the fields you want to use. For example, drag Date to Rows and Price to Values.
  2. Check the value calculation. Excel commonly sums numeric fields by default. For daily prices, an average, minimum, or maximum may be more meaningful than a sum.
  3. Open the drop-down beside the field in the Values area, choose Value Field Settings, and select the calculation that fits the question.
  4. Drag fields such as Currency, Instrument, or Source to Filters or Columns if you want to compare or narrow the summary.

The same basic workflow is available in Excel for Mac, though the ribbon and PivotTable pane may look different. Excel for the web also supports PivotTables, but the available controls and layout can differ from the Windows desktop version. If an instruction depends on a specific dialog or feature, check the options shown in your version rather than assuming every interface is identical.

Choose useful summaries and layouts

The arrangement of fields determines what the PivotTable says. For instance, Date in Rows and Price in Values creates a date-based summary. If there is exactly one record per date, Average of Price will match that record’s price; if there are multiple records per date, it will average them. Sum of Price adds price observations together, which is usually not a meaningful way to describe a market price. The calculation should reflect the structure of the source and the question being asked.

Other useful layouts include putting Instrument in Rows and Date in Columns to compare instruments across dates, or putting Currency in Filters so a reader can select one currency at a time. For transaction records, a count of records can show how many entries fall into each category, while a sum of quantity can total recorded quantities. These summaries describe the data in the workbook; they do not establish that the records are complete or representative.

Use Value Field Settings to change Sum to Average, Count, Min, or Max where appropriate. If a field that should be numeric is summarized as Count, Excel may have imported some values as text or may be interpreting the column inconsistently. Correct the source data and refresh the PivotTable rather than treating Count as a substitute for a price calculation.

Group dates and examine time periods

Daily data can create a long list of rows. If Excel recognizes the Date field as dates, right-click a date inside the PivotTable and choose Group. Depending on the selection and the data, Excel can group dates by periods such as months, quarters, or years. Grouping can make a multi-year dataset easier to review, but it changes the level of detail shown: a monthly average is not the same as the last daily price in that month.

For a summary by month, place Date in Rows, then group it by Months and Years when both are needed. Including Years helps avoid blending the same month across different years into one category. Choose the value calculation deliberately: an average summarizes the observations within each group, while a maximum reports the highest recorded value in the group. Neither automatically identifies the final observation of the period.

If Group is unavailable or Excel reports that it cannot group the dates, inspect the source column for text dates, blank cells, errors, or inconsistent entries. A single non-date value can prevent grouping. Correct the source column so every populated entry is a valid date, then refresh the PivotTable. If the data is intraday, consider whether grouping only by calendar date would hide important differences in observation time.

Use a PivotChart carefully

A PivotChart can make a summary easier to scan. Click inside the PivotTable and choose PivotTable Analyze > PivotChart, then select an appropriate chart type. For a time series, a line chart is often a natural choice when the horizontal axis represents dates and the values are comparable. A chart can also reflect PivotTable filters, so changing a currency or instrument selection may change the displayed series.

Check the chart’s level of detail and labels. A chart of monthly averages should be labeled as monthly averages, not simply as daily prices. Keep currencies and units visible, and avoid plotting incompatible series on a shared scale without explaining the difference. A visually smooth line can conceal missing days, source changes, or a shift from one contract or instrument to another. The chart presents the selected data; it does not validate the data source.

Refresh the PivotTable and troubleshoot errors

A PivotTable is a summary of its source, not a live copy of every change. When source values change, click inside the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables. A right-click inside the PivotTable also offers Refresh in desktop Excel.

If the source is an Excel Table, newly appended rows are included in its expanding range, but the PivotTable still needs a refresh to incorporate them. If the source is an ordinary fixed range, rows added below that range may be left out. In that case, adjust the PivotTable source using PivotTable Analyze > Change Data Source, or convert the source to a Table for future additions.

  • Dates appear as separate text labels: check that Excel recognizes the source values as dates, then refresh and group again.
  • Prices are counted instead of averaged or summed: check for numbers stored as text, symbols mixed into the column, or inconsistent entries.
  • The same category appears more than once: look for leading or trailing spaces, spelling differences, and inconsistent naming in the source.
  • New records do not appear: confirm the source range includes them and refresh the PivotTable.
  • A price total looks implausible: check whether Sum is being used on observations that should be averaged or otherwise summarized.

Interpret results without overclaiming

A PivotTable is a descriptive tool. It can organize recorded values and calculate summaries, but it cannot tell you whether a data provider used a particular pricing convention, whether a quote was executable, or whether an apparent change has a particular cause. Keep the source name, retrieval date, instrument, currency, and unit with the workbook. If a provider revises historical records, the refreshed summary may differ from an earlier one.

Be especially cautious when comparing sources or instruments. A spot quote, a futures contract with a specified delivery month, and a fund share can move differently because they represent different things and may use different conventions. Before combining records, establish that the dates, units, currencies, and definitions are compatible. If they are not, analyze them separately or make the distinction visible in the PivotTable.

For example, a monthly average built from daily observations gives each included observation equal weight unless the source or method says otherwise. It is not automatically a month-end quote, and it may not account for missing observations. A useful report states the method in plain language, such as average of recorded daily prices, along with the currency and unit. That small clarification prevents readers from mistaking a summary statistic for a different market measure.

Frequently Asked Questions

Can Excel create a gold-price PivotTable automatically?
Excel can create the PivotTable structure, but you must first supply or import a dataset. The PivotTable summarizes the records it receives; it does not fetch a gold-price history by itself.

Should I use Sum or Average for gold prices?
For a price series, Average, Min, or Max is usually more interpretable than Sum. If there is one observation per date, Average returns that observation; if there are multiple records per date, it averages them. Choose based on what each source row represents.

Why will Excel not group my gold dates by month?
The Date column may contain text, blanks, errors, or mixed formats. Make sure all populated entries are valid Excel dates, refresh the PivotTable, and try grouping again.

How do I add new gold-price rows to a PivotTable?
Add the records to the source Table or update the fixed source range, then refresh the PivotTable. A Table expands as rows are added, but the PivotTable still needs refreshing to show the new records.

Can I compare gold prices in different currencies in one PivotTable?
You can separate or filter records by currency, but values in different currencies should not be treated as directly comparable prices. Convert them consistently using an appropriate exchange-rate dataset if a cross-currency comparison is needed, and retain the original currency and conversion details.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.