Peltier Tech

Finally a practical use for the obscure BAHTTEXT formula: an easy formula to convert numbers to words. Thanks to my colleague Liam Bastick.

Best Excel Tip Ever? | SumProduct are experts in Excel Training: Financial Modelling, Strategic Data Modelling, Model Auditing, Planning & Strategy, Training Courses, Tips & Online Knowledgebase You may even have encountered this problem for yourself.  Microsoft suggests you write VBA code to create a user-defined function (let’s not assume they’d actually create a function!):

Dynamic Arrays, Names, and Multi-Tier Categories

Despite the ease of using Dynamic Arrays to populate dynamic charts, you may wish to define Names based on the Dynamic Arrays, and build a dynamic chart with these Names. This article extends the process of defining Names based on Dynamic Arrays by applying them to a chart with multi-tiered categories.

Dynamic Arrays, Names, and Multi-Tier Categories - Peltier Tech Despite the ease of using Dynamic Arrays to populate dynamic charts, you may wish to define Names based on the Dynamic Arrays, and build a dynamic chart with these Names. This article extends the process of defining Names based on Dynamic Arrays by applying them to a chart with multi-tiered categori...

New Excel Formula Editor

If you've ever wrestled with Excel's formula bar, you know how frustrating it can be.

Limited space, lack of formatting, debugging difficulties, and no real-time error detection can make writing anything beyond basic formulas a tedious task.

However, there's a game-changing solution available: the Advanced Formula Environment (AFE) developed by Microsoft.

Read this article about the Advanced Formula Environment by my colleague, Excel MVP Mynda Treacy.

New Excel Formula Editor The new Excel Formula Editor enables you to more easily write, debug, and manage formulas with a modern interface.

Dynamic Arrays, Names, and Charts

Despite the ease of using Dynamic Arrays to populate dynamic charts, you may wish to define Names based on the Dynamic Arrays, and build a dynamic chart with these Names. This article walks through the process of defining Names based on Dynamic Arrays and applying them to a chart.

Dynamic Arrays, Names, and Charts - Peltier Tech This article walks through the process of defining Names based on Dynamic Arrays and applying them to a chart.

My previous tutorial introduced a protocol to create arrow charts. Let's modify this approach to replicate a New York Times chart, showing changes in foreign-born populations from 1990 to 2020.

Age of Migration Arrow Chart - Peltier Tech Let's apply our arrow chart protocol to recreate a New York Times chart, showing changes in foreign-born populations from 1990 to 2020.

Arrow Charts in Microsoft Excel

Arrow charts enhance comparisons by showing the direction of changes. This tutorial shows an easy way to create horizontal arrow charts in Excel.

https://peltiertech.com/excel-arrow-charts/

This tutorial describes several VBA methods to insert a chart into your worksheet. Use Shapes.AddChart2; the rest are obsolete and make charts that look dated.

Use VBA to Add a Chart - Peltier Tech This tutorial describes several VBA methods to insert an Excel chart. Use Shapes.AddChart2; the rest are obsolete and make charts that look dated.

Formula Charts: In-Cell Charts Using Worksheet Formulas

Formula Charts are small charts built with simple formulas and displayed in a tabular array of worksheet cells. This definitive guide to formula charts shows bar chart and dot plot types with several variations.

Formula Charts - Peltier Tech This tutorial shows how to use simple formulas to build Formula Charts, small in-cell charts displayed in a tabular array of worksheet cells.

Target vs Actual Dumbbell Plot

Many of the charts we create compare actual performance to a predefined target. This tutorial shows how to create a "dumbbell" chart that compares actual and target levels of performance, and clearly highlights which performers surpassed their targets and which fell short.

Target vs Actual Dumbbell Plot - Peltier Tech Many charts compare actual performance to a predefined target. Here's how to create a "dumbbell" chart that compares actual and target performance.

Good Chart Data

Everybody loves their Excel tips and tricks. I know I’m famous for my extensive collection of Excel charting tricks. But the most important charting trick is Get The Data Right. The secret of successful Excel charting begins and ends with Good Chart Data.

A couple of years ago I wrote an article describing best data practices for charting in Excel. It is a cornerstone post on my blog, it covers a topic that will never get old, and it bears repeating.

Good Chart Data The most important chart trick is Get The Data Right. You will save time and make better charts if you use Good Chart Data. Here are characteristics of Good Chart Data.

Pi Day article about Pie Charts

F**k it, let's use pie charts - PolicyViz People hate pie charts. They may not know exactly why and that may not be important. But, I'm now thinking, F**k it, let's go ahead and make some pie charts.

Lambda Formulas to Calculate Easter

This article presents Lambda formulas that calculate the date of Easter according to Western and Eastern Orthodox calendars.

Lambda Formulas to Calculate Easter - Peltier Tech This article presents Lambda formulas that calculate the date of Easter according to Western and Eastern Orthodox calendars.

Lambda to Unstack Data Range into Multiple Chart Series

Often multiple sets of X and Y values are stacked, then plotted as one series. Here is a Lambda formula to unstack into multiple data series for charting.

https://peltiertech.com/lambda-unstack-data-range-multiple-chart-series/

Target vs Actual Thermometer Chart

This tutorial shows how to make a thermometer chart. Our Target series is the container while the Actual series is the expanding fluid within that shows its level compared to the target. Add enhancements that label and color-code actual values above and below the target.

https://peltiertech.com/target-vs-actual-thermometer-chart/

Pareto Charts in Excel

The Pareto chart is a convenient, easy tool to show which inputs of a process have a greater effect on its outputs. This tutorial shows how to build several Pareto chart styles.

https://peltiertech.com/pareto-charts-in-excel/

Multiple Rows or Columns as Chart Series Data

Excel throws an error if you use multiple rows or columns as chart series data. Get around this problem with Dynamic Arrays (and more).

Multiple Rows or Columns as Chart Series Data Excel throws an error if you use multiple rows or columns as chart series data. Get around this problem with Dynamic Arrays (and more).

Build a Chart Export feature with me at Excel Weekend. Learn how to create a minimally useful feature, and incrementally add enhancements—error handling, flexibility, a Save As dialog, and more—to make it ready for your least experienced users. https://mailchi.mp/35266f55854c/build-a-chart-export-feature-with-me-at-excel-weekend

Enhance Your Excel Charting Experience | Jon Peltier, Thu, Jan 11, 2024, 5:00 PM | Meetup **SESSION TRACK:** Excel **SESSION OUTLINE:** Since 2008, Peltier Technical Services has offered commercial add-ins to enhance the Excel charting experience. Now in versio

The Text Import Wizard has been part of Excel for decades. For heavy use, Power Query has become a more powerful tool for importing and managing large amounts of data. But the wizard is a quick and useful way to import CSV files cleanly into Excel, avoiding any issues with data corruption.

https://peltiertech.com/text-import-wizard/

I'll be in London Wednesday after work to present "Interactive Charting in Excel".

Excel is a powerful tool for data analysis and business intelligence, and Excel's charts are a powerful and flexible tool for visualizing analysis results.

Extend the power and flexibility of Excel charts by making them interactive. You can use controls or slicers to allow users to select what to see in a chart. This presentation covers several ways to make your charts interactive, and how to overcome drawbacks in some techniques.

For details, see https://www.meetup.com/London-Excel-Meetup-Group/events/292493629/

Announcing Live Self Service BI & Data Visualization Boot Camps

I'm extremely excited to announce that I am teaming up with Ken Puls of Excelguru to lead LIVE, IN-PERSON versions of their Self Service BI and Data Visualization in Excel Boot Camps!

https://mailchi.mp/26b82ce68d7e/announcing-live-self-service-bi-data-visualization-boot-camps

Peltier Tech Newsletter:
Interactive Charts, Animating Charts, and Animated GIFs; Recent and Upcoming Speaking Engagements; and More.
https://mailchi.mp/db6779446aa3/interactive-and-animated-charting-jon-speaks-and-more

Charting Dynamic Arrays, Unblocking Macros, Charts for Researchers, Live Excel Training, and What's New in Excel - https://mailchi.mp/2a336e85ba77/charting-dynamic-arrays-unblocking-macros-charts-for-researchers-and-more

Charting in the Age of Dynamic Arrays | Jon Peltier, Thu, May 4, 2023, 5:00 PM | Meetup **SESSION TRACK:** Excel **SESSION OUTLINE:** With the introduction of Lists in Excel 2003, which became Tables in Excel 2007, charting in Excel has benefited from easier

From my colleague, Excel MVP Jon Acampora:
Five Reasons to Use Excel Tables.
But you're using Tables, aren't you?

5 Reasons to Use Excel Tables - Excel Campus Excel Tables can make your work simpler and faster! Learn five reasons why you want to use Excel Tables in your worksheets.

More Moving Averages, Exploratory Data Analysis, and a Weekly Newsletter about Charts. - https://mailchi.mp/5eebce5b73e0/more-moving-averages-exploratory-data-analysis-and-a-weekly-newsletter-about-charts

Excel vs Google Sheets, What to do Before Memorizing Shortcuts, Peltier Tech Charts for Excel - Peltier Tech Newsletter 2023 03 22 - https://mailchi.mp/223876ab5f58/excel-vs-google-sheets-more-shortcuts-peltier-tech-software

Blog Index, Excel Tables, and What's New in Excel - Peltier Tech Newsletter.
I've finally compiled an index of the various topics on my blog to help you find important articles. Excel MVP George Mount explains why you should use Tables (you DO use Tables, right?). And Microsoft keeps making improvements to Excel.
https://mailchi.mp/89f055be0dd3/blog-index-excel-tables-and-whats-new-in-excel

Check out improved Lambda moving averages, a new function to imports image into your worksheet, and Jon's Best Charting Tips from Bulgaria Excel Days - https://mailchi.mp/934d4fb993ed/more-lambda-functions-images-in-excel-and-best-charting-tips-from-bulgaria-excel-days

Peltier Tech Newsletter: A new blog post on LAMBDA functions, a video of my best charting tip(s) of 2022, and Power Query comes to Excel for Mac.
https://mailchi.mp/970a6f4afd44/lambda-functions-charting-tips-and-power-query-for-mac