[Libs-Or] Tech-Talk: EXCEL - "Calculated Fields" in a PivotTable

Wed Nov 19 14:40:42 PST 2025

Welcome to the latest issue of Tech-Talk!

Having trouble reading this email?
You can view the tech tip<https://www.tech-talk.com/calculated-fields-in-a-pivottable.html> and the communications tip<https://www.tech-talk.com/get-someone-to-speak-up.html> online instead at:
https://www.tech-talk.com/login/oregon

When prompted for a username and password, use ORLIBTECH for both.

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.

[https://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png?rdr=true]

[https://files.constantcontact.com/ee1208b4001/9193943c-bd3b-4204-835d-e45fa5459f4e.png?rdr=true]<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxq0SeRDyjQ5lp3S7WiFvvA6cLHmerX4gmy-loVw7MjL0xHn0tt3sum23N3TumTpyrowkS_oJzmtUakwJ6ZhCLorIA7MPQWPMBDny31J4uL0okr2G-zql_mJb602Jd5-Afvboy9_q6Z7cZfVEd3yC9qNA=&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

Photo by dlovan 666<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxq0SeRDyjQ5lpHPL5Hs8JSWfZfWzZBruy5WEHD4X-l0xk8uniNpsXE25AEZ84PHdfJAl4W3BAxE2MedHVjIyUWlwakO-kUplIf8DcsKlSEa4B9v7U3sSfoqWTN9BI4YJ-sSSSpD4_dAeqUBUb5Pr_TuRLFzOXja9aPKwCMudJ2fc1tgpgjw1Qgy2n-RTccURmYY3JJWLxbPkpl8lrrQnivag=&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==> on Unsplash<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxq0SeRDyjQ5lpZMCu9EJ6I_xRU9bOV7-usPKeyIFpK-d-XAKyJTVY1Y-nwsE0FPgYx-KDbg5sNV8s_8R04YfCaignaAG1hBosOphrkd7wic5CAtBRFNsm0DKYEnZ4FGdk1IAdboGzoS9yjecjExHrBMjb6DPA0DeXsTD1f-n4j7YTW6RcRymXkWKYgCOV_ezPAMyH_13x-oy_U6tPnYQPGds91eNw8uvGhqAFFeH6h6B9s_LvJu98uUU70iN3XHB7-GnE0Di-sBqt&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

[https://files.constantcontact.com/ee1208b4001/e25f6bcf-26a8-4e59-91f2-cc248962042d.png?rdr=true]<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxqw90dJRPPmXgNnxUBviEFJG0LXjGa9oDTwFlumkc-Z9Wc7dUKLWqGL_hECgF-vQnr5tQHntr33Od9ZghbSw-KUtwFmhry9CZbbqa6Ll4XhWsIX6pKyOrUZlbMspV4d1shLXmZTK6bL2-p0LTlj-bG44BYRxbBjhSvA==&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

Webinars for You

NOTE: Webinars begin at 3 pm ET / 2 pm CT / 1 pm MT / 12 Noon PT and are one hour long.

November 19: [INTERNET] Internet Search Techniques: Search Smarter, Save Time. Why Attend: You want faster and more efficient search results.

December 3: [EXCEL] Spreadsheet Storytelling: Interactive Features You Need. Why Attend: You want tips on bringing out your messages.

December 17: [GRAPHICS] GIF Animations for Images, Websites and Social Media. Why Attend? You want your graphics to grab attention.

January 14: [CYBERSECURITY] Say Goodbye to Password Hassles with Passkeys! Why Attend? You want safer logins for your online accounts.

View Webinars and Register Here<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxq4kKo3PHWJjJqnwaJ2e72e18HzQnAoEW9pds5LvPk5oK0WOKy46qMSSGpmc4ogh8yJtlHWQrpJrSj7Cq83V-NuA_5Js-OKoAo08mkaOzNEYkO95IAnL7Tc0=&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

EXCEL - "Calculated Fields" in a PivotTable

Advanced

[https://files.constantcontact.com/ee1208b4001/e240dfc1-25c4-4031-815e-1b89c6fc9184.png?rdr=true]

You’ve mastered creating PivotTables in Excel for quick summaries of your data. The built-in calculations like Sum, Count, Average, Min, or Max are great, but sometimes you need more custom insight than those basic calculations. That’s when adding your own formula can help!

Why Use Calculated Fields?

1. A PivotTable can easily total things like “Books Checked Out” or “Program Attendance,” but if you want something more customized—like “Percent of Patrons Who Attend Programs” (Program Attendance ÷ Total Patrons)—you can create that with a Calculated Field.

2. You could calculate “Average Checkouts per Patron” by dividing the total number of checkouts by the number of active library members.

3. If new circulation data or program statistics are added to your source file, your Calculated Field instantly updates the results—no need to redo the math.

Using your own formulas lets you turn raw numbers into insights. You don’t have to change how you collected your data or leave the PivotTable environment. It is the difference between summarizing your data… and analyzing it!

Once you have your data set up in the PivotTable, there are a few things you can do to examine it deeper to get that custom insight.

Go Beyond the Basic Tools

When you first create a PivotTable, it’s easy to stop at the totals…total sales by month, total cost by region, and so on. But totals alone don’t always tell the full story.

That’s where Calculated Fields come in handy! They let you ask questions of your data and get answers right inside the PivotTable.

For example, suppose your table tracks the number of library visitors and program costs for four branches… North, South, East, and West… over four months. You can already see which branch has the most visitors.

However, that doesn’t reveal which branch runs programs most efficiently. With a Calculated Field, you can create a new measure that shows cost per visitor ... giving you a clearer picture of which branch makes the best use of its budget.

[A table shows library visits program costs and cost per visitor by region East North South and West. Totals are 40325 visits $14063 cost and $0.3487 cost per visitor.]

How to Add a Calculated Field

You do not have to change your original data or add extra columns to get new calculations. Here’s how to add one:

[A dialog box titled Insert Calculated Field shows a new field named Cost Per Visitor with the formula equals Program Costs divided by Library Visits. Library Visits is selected in the list of available fields.]
1.  Click anywhere inside your PivotTable.
2.  Go to the PivotTable Analyze tab and select Fields, Items & Sets.
3.  Choose Calculated Field.
4.  In the Name box, give your Calculated Field a title.
5.  Then either type in a mathematical equation in your formula box, or highlight the field name and choose Insert Field instead.
6.  Select Close to see your new Calculated Field added to your PivotTable.

CAUTION: You must use the field names exactly as they appear in your data.

Now, everything happens right inside the table. The Calculated Fields will adjust automatically when new data is added or filters are applied.

Excel Web 365

While the Web 365 version of Excel allows you to create a PivotTable, it does not let you create a Calculated Field.

However, there are workarounds you can do depending on the type of data you have and the formulas you need. Here is one solution ... if you have access to Excel Desktop. This combo (web and desktop) will give you full functionality, when you need totals-based calculations.

Try this workaround:
1.  In the Excel Web App, go to File → Open in Desktop App (if you have it installed) ... or take your document to a computer that has Desktop Excel.
2.  Create your Calculated Field there (PivotTable Analyze → Fields, Items & Sets → Calculated Field).
3.  Save your workbook; add it to your Web 365 — the Web version will keep the Calculated Field, even though you can’t edit it online.

This preserves and displays existing Calculated Fields in the Web version

Upcoming Webinar!

[https://files.constantcontact.com/ee1208b4001/09948adc-f99f-4d9a-8a2b-e41881db953d.png?rdr=true]
Excel Storytelling: Interactive Features You Need

Level: Intermediate-Advanced
·     Conditional formatting
·     Calculated Fields
·     Dropdowns; Check boxes
·     "Format as Table"

Wednesday, December 3rd!<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxq4kKo3PHWJjJqnwaJ2e72e18HzQnAoEW9pds5LvPk5oK0WOKy46qMSSGpmc4ogh8yJtlHWQrpJrSj7Cq83V-NuA_5Js-OKoAo6nqET3xevsgwoulG98fgic=&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

Google Sheets

[A settings window titled Calculated Field 1 displays a formula equals Program Costs divided by Library Visits with Summarize by set to SUM and Show as set to Default.]
1.  Click anywhere inside the PivotTable to reveal the Edit button (it may look like a pencil icon) and click it. (If the Edit button isn’t visible, right-click the pivot table and choose Show edit control to make it appear. Then, click the Edit button to open the editor.)
2.  In the PivotTable editor, look for the Values section. Click Add.
3.  Select Calculated Field.
4.  In the new box labeled Formula, type your custom formula using the names of your existing fields.
5.  Press Enter.
6.  To rename the formula box, select a cell with that title in your PivotTable. Then type the new name…it updates automatically.

[https://files.constantcontact.com/ee1208b4001/e565d7d1-215e-487a-8e78-f332113e7825.jpg?rdr=true]

Communications: Engaging

Get someone to speak up

If you're like me, you may have someone in your life who is shy, withdrawn, or just hard to reach. Getting them to speak up is a challenge.

Asking them a typical question is met with a minimal response ... a grunt, shrug, or one-word answer ... making normal conversation impossible.

What's the answer? I have to change how I communicate, not just what I say. In particular, I need to not ask open-ended questions (which, in other situations works very well).

Open-ended questions can overwhelm quiet communicators.

Instead, it's better to ask “either/or” or “on a scale” question. Make it easy for them to choose a response.

Example:
·     “Was your day more calm or chaotic today?”
·     “On a scale from 1 to 10 — how tired are you?”

Then when they respond, you can gently expand on what they said, such as:
·     “Hmm, a 4? What made it that kind of day?”

Scenario: My tech support person is extremely non-communicative ... though she obviously wants to be helpful. Ideas for getting the info I need:
·     "On a scale of 1 to 10 (10 high), how difficult is this going to be to fix?"
·     "To help you, is it better to write out the issue or show you what is happening?"

Just shifting my style of communicating with her to one of offering specific choices, rather than open-ended questions, makes our conversations more productive and has her opening up a bit more.

[https://files.constantcontact.com/ee1208b4001/0091e766-eacd-41a9-87a5-f6588196ba86.png?rdr=true]<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxqypZwx4v0BZKaMN0juL8I41kDORHcor7Yq_lyVoQp1fvNfQBcYqKoYtByHuBTqJUdw_YdZCMjpvodndACpLLWKwaQ_gTc_Qkga3zx6NM2Kkq&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

www.Tech-Talk.com<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxqypZwx4v0BZKaMN0juL8I41kDORHcor7Yq_lyVoQp1fvNfQBcYqKoYtByHuBTqJUdw_YdZCMjpvodndACpLLWKw5AnXWsLBLNzFN5Vt2s7xC&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

When was the last time you visited the Tech-Talk online resource? It has all kinds of fun ways to solve issues, discover new ideas, and hone your skills!

[https://files.constantcontact.com/ee1208b4001/207827a7-4ca9-4076-97ff-fa3c1919ea4f.png?rdr=true]

We're here to help!

Tell us what you are unclear about ... a new technique you'd like to learn ... or some fun tool you've heard about. Use this form!<https://opiayfbab.cc.rs6.net/tn.jsp?f=001ju7Upp_cZIxmpAdOQ_bvye9In1Sg6XWtCJcN0z5so6FBR0lleKAxq3J7-AUhFoZZfPQToVJuZzRgw7Fa8giWsTTJ2EBVfhlE6JdTymi13HigUUQeTUTkf_IcfKKjV8-ECeaBx39W99s13F_SOrJjsTdDXD2hi448Jg0UGaFs-_o=&c=O9GiVGD3Yh1KFOwkLWvSUthyPj2pJLkKyi7mAInLgz4lwGxJDglF8A==&ch=E-t6e588IwSTaohW6KkTA3N9sRpRm4hXxpphOhqSJsLtWVfNS252Qw==>

Copyright 1996-2025 Shared Results International. Published weekly. Distribution is limited by license. For information on how to include additional recipients, contact support at tech-talk.com<mailto:support at tech-talk.com> 585-615-7795. Cheers,
Darci Hanning, MLIS (she/her/hers)
Public Library Consultant / CE Coordinator
Continuing Education Resources: https://slo.oregon.gov/conted/
State Library of Oregon | Library Support and Development Services
971-375-3491 | darci.hanning at slo.oregon.gov<mailto:darci.hanning at slo.oregon.gov> | www.oregon.gov/library<http://www.oregon.gov/library>

[State Library of Oregon (Logo)]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20251119/ae3138f4/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20251119/ae3138f4/attachment.png>