Excel Headlines

Video: Smart Macro Recording
I presented during the Excel Virutally Global conference in July 2020, Here's the recorded video: Smart VBA Macro Recording
Mon, 24 Aug 2020 16:15:00 GMT

Updated article: Prevent Open Event
I have updated my existing article on how to prevent Open events from running as Microsoft has changed the way Excel responds to the Shift key being held down (it now ignores it).
Thu, 13 Aug 2020 16:15:00 GMT

Nieuw: ons Excel Forum
Wij kregen tamelijk veel Excel gerelateerde vragen in onze commentaren. Om deze vragen beter te kunnen kanaliseren zijn we daarom een Excel forum gestart. Heb je een vraag? Word lid van ons forum en stel hem hier: Excel Experts Nederland Forum
Mon, 30 Jan 2017 16:38:13 +0000

Opening a File from a Userform Disables Ribbon
In Excel 2019 16.0.10361.20002 32-bit, when I open a file from a userform (which for me is always), the hotkeys on the Ribbon don’t work. Here’s how I reproduce: In a new workbook, add a userform named UserForm1. Add a commandbutton to the userform. Paste this code in the userform’s code module. [crayon-5fbcb2fed381d956729152/] Add a […]
Wed, 09 Sep 2020 20:32:26 +0000

Introducing the Measure Monkey

You know the drill… extract, transform and load your data, relate your tables, then create basic DAX measures.  All work that needs to be done before you can really get started on analyzing your data.  Today we’ve unleashed the Measure … Continue reading →

The post Introducing the Measure Monkey appeared first on The Excelguru Blog.

Fri, 23 Oct 2020 18:14:37 +0000

Conditional Donut Chart

Create a donut chart, plotting extra formatted segments and using formulas to show and hide these segments, to achieve the conditional formatting appearance.

The post Conditional Donut Chart appeared first on Peltier Tech Blog.

Wed, 21 Oct 2020 17:26:36 +0000

Combine Text and Formatted Numbers in Excel

Written by Debra Dalgleish from Contextures Blog

In all versions of Excel, you can use a simple formula, with the & operator, to combine values from different cells. If you want the numbers formatted a certain way, use the TEXT function to set that up. Video: Combine Text and Numbers First, this video shows a simple formula to combine text and numbers … Continue reading "Combine Text and Formatted Numbers in Excel"

Combine Text and Formatted Numbers in Excel is a post from Contextures Blog and is not allowed to be copied to other sites

Thu, 12 Nov 2020 05:02:00 +0000

LET - Names in Formulas - Generally Available

Earlier this year we announced the release of a new function, LET, which allows you to give names to calculations or expressions in your formulae (Announcing LET). As of earlier this month, the LET function is now generally available within the Production audience!

 

Thanks to all of our Insider's for using the LET function and giving us feedback! As a result we've made a few changes that we'll outline below. Additionally, we’d like to share some best practices to consider when using the function or writing formulas in general!

 

Accessing LET Today

To get access to LET, please make sure you have updated to the latest version of Excel. 

LET is available to Excel subscribers with Office 365 or Microsoft 365. The feature is currently available on the Current Channel across all platforms and generally on the web.

 

Specifically versions of Excel greater than or equal to:

• Windows: Version 2009 (Build 13231.20262)
• Mac: Version 16.42 (20101102)

What version of Office am I using?

 

Changes made to LET

1. Autocompletion of names

We added a quality of life improvement whereby autocomplete will special case names defined by a LET with a unique icon and entry.

 

In the following example, you can see the special icon  indicating that you can autocomplete your formula with the count name:

 

=LET(count, 123, count

LET GIF

 

2. Allowed Names

"." is no longer a legal character in names bound by a LET. If you happen to have a pre-existing workbook which has a "." in its name, it will be silent upgraded to a legal name.

 

The reason for this change was due to conflicts which could occur within “dot notation� for dereferencing Data Types.

 

To learn more about what names are allowed, please see the Help topic for LET.

 

3. Localization changes

Thanks to feedback from the community, we have removed localization for the LET function. LET is the name of the function in all SKUs of Excel and not localized.

 

Best Practices when Authoring Formulas

With the addition of LET, there are some new best practices we have found useful when authoring formulas, in general and with LET. We'd love to hear more from you if there are other best practices you have employed!

 

Example Scenario: Converting and formatting Temperature Values

For the below examples, feel free to follow along and try it out for yourself with the included sample data. The example LET fills out the values in the Fahrenheit column

 

Day	Celsius	Fahrenheit
January 1	4.68
January 2	4.49
January 3	4.4
January 4	4.71

 

Line Breaks

In desktop versions of Excel, you can add line breaks to your formulas using the “ALT+Enter� shortcut while authoring. We like to make use of this functionality and add an additonal indenting of white space, by hitting "spacebar" 5 times, to help make the formula easier to read when defining names.

 

Formula without line-breaks

=LET(Celsius, B2:B5, ConversionFactor, 9/5, FValue, (Celsius*ConversionFactor)+32, FValueRounded, ROUND(FValue,2), FValueRounded&"°F")

 

Formula with line-breaks and white spacing

=LET(CelsiusValue, B2:B5,      ConversionFactor, 9/5,      FValue, (CelsiusValue*ConversionFactor)+32,      FValueRounded, ROUND(FValue, 2),      FValueRounded&"°F" )

 

Documenting your logic

The formula we have created does not benefit from any performance gains when using a LET. It does, however, gain improvements in documenting the intent of the formula and what each individual calculation does. This allows for easier debugging of more complex expressions and allows you to more easily document your work.

 

Consider the aforementioned formula with no LET

=ROUND((B2:B5*9/5)+32,2)&"°F"

 

While it is true that the above formula is shorter, it does not do a good job of conveying the intent of the calculation. With a LET, it is much easier to understand all the individual components and transformations that the value goes through as it gets converted. Simply read the formula, line by line.

 

=LET(CelsiusValue, B2:B5,      ConversionFactor, 9/5,      FValue, (CelsiusValue*ConversionFactor)+32,      FValueRounded, ROUND(FValue, 2),      FValueRounded&"°F" )

 

Debugging and Updating Formulas

The last major benefit LET provides is with debugging and/or updating your formulas. Let’s take the previous example and imagine, while authoring the formula, we got to a state where we have the Fahrenheit value calculated and wanted to simply append "°F" to the end.

The formula might look something like this:

 

=LET(CelsiusValue, B2:B5,      ConversionFactor, 9/5,      FValue, (CelsiusValue*ConversionFactor)+32,      FValueRounded, ROUND(FValue, 2),      FValueRounded&"°F" )

 

This formula will work fine but immediately we realize there are too many significant digits in our result:

 

Fahrenheit

40.424°F

40.082°F

39.92°F

40.478°F

 

At this point, all that’s needed to do is round the value and so we update the formula:

 

=LET(CelsiusValue, B2:B5,      ConversionFactor, 9/5,      FValue, ((CelsiusValue*ConversionFactor)+32)&"°F",      ROUND(FValue, 2) )

 

We then run into an issue because we are suddenly getting #VALUE! as a result…

That’s OK, though. With LET, we can more easily see where the error is occurring by testing different parts of the formula.

 

Breaking apart FValue, which seems to be the source of the error, we can return FValue and see what is getting fed into the ROUND function:

 

=LET(CelsiusValue, B2:B5,      ConversionFactor, 9/5,      FValue, ((CelsiusValue*ConversionFactor)+32)&"°F",      result, ROUND(FValue, 2),      FValue )

 

This will of course return the previous results we saw before:

Fahrenheit

40.424°F

40.082°F

39.92°F

40.478°F

 

Depending on your formula authoring abilities and proficiency with debugging formula errors, the problem may jump out immediately to you: The issue is that we are returning a string and the ROUND function expects a number.

 

However, if it isn’t immediately obvious, you can further debug the statement by re-writing the LET to return the various components of FValue: CelsiusValue and ConversionFactor. These will both return numbers which means that the error lies in the expression defining FValue.

 

The offending line is clearly the appending of "°F": &"°F" and all that’s needed to do is append the value after rounding which will give us the final formula:

 

=LET(CelsiusValue, B2:B5,      ConversionFactor, 9/5,      FValue, ((CelsiusValue*ConversionFactor)+32),      result, ROUND(FValue, 2)&"°F",      result )

 

While the above is valid, one last tip would be to simply add another name for the string conversion, in case you want to add an IF statement which returns both an unformatted and formatted value for Fahrenheit based on a condition in the future.

 

=LET(CelsiusValue, B2:B5,      ConversionFactor, 9/5,      FValue, ((CelsiusValue*ConversionFactor)+32),      FValueRounded, ROUND(FValue, 2),      FValueStringAppended, FValueRounded&"°F",      FValueStringAppended )

 

Learn More

To learn more about LET, please check out our help article and in the meantime we are excited to hear more from you about the ways you have used LET in your own workbooks!

LET Help

 

Availability Notes

LET is now available to Excel subscribers of Office 365 or Microsoft 365 in Production Current Channel.
To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also Excel on Facebook and Twitter. 

 

Chris Gross
Program Manager, Excel

Mon, 16 Nov 2020 12:10:56 PST