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-5f6af225efdfe702866883/] Add a […]
Wed, 09 Sep 2020 20:32:26 +0000

Update to Monkey Tools QuerySleuth

We've been kind of quiet here, but we're excited to announce that we've just published an update to Monkey Tools QuerySleuth feature.  It now contains an "tabbed" experience so that you can easily flip back and forth between queries, "pinning" … Continue reading →

The post Update to Monkey Tools QuerySleuth appeared first on The Excelguru Blog.

Thu, 10 Sep 2020 21:32:30 +0000

Use Power Query to Manipulate Your Data

Power Query (Get & Transform) is a powerful yet easy technique for manipulating data in Excel. This example shows how to prepare data for a box plot.

The post Use Power Query to Manipulate Your Data appeared first on Peltier Tech Blog.

Thu, 07 May 2020 13:57:08 +0000

Excel Check Box Fills in Billing Address

Written by Debra Dalgleish from Contextures Blog

There's an Excel Order Form tutorial on my Contextures site, with written steps and a video that show how to set it up from scratch. I've just added a new section, with a check box that fills in the billing address. Add a check mark, and a macro copies the shipping address for you. Billing … Continue reading "Excel Check Box Fills in Billing Address"

Excel Check Box Fills in Billing Address is a post from Contextures Blog and is not allowed to be copied to other sites

Thu, 17 Sep 2020 04:01:20 +0000

Excel performance improvements now take seconds running Aggregation functions

 

I’m excited to announce that faster Aggregation and RealTimeData functions are available in the latest¹ Microsoft 365 Monthly and Semi-annual preview channels! This is our next wave of Excel M365 performance improvements after Speedy Lookups to reinforce our commitment to making Excel in Microsoft 365 the new gold standard of Excel Performance. This time around we have even more improvements, starting with...

 

Aggregation functions like SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS and their singular counterparts like SUMIF, COUNTIF, AVERAGEIF are among the most used Excel functions. If you use them to aggregate items in a table or range in Excel, and see it noticeably take time in seconds or minutes or more, chances are you'll now see very noticeable improvement in the speed at which you see results.

We made them faster by more efficiently evaluating each expression for a range of cells . We create an index on-demand when you first search a range of cells for an expression and then reuse it in subsequent aggregations pulling from the same range, until data changes in the lookup range. For details refer to our updated Excel Performance and limit improvements whitepaper.

 

RealTimeData (RTD) is commonly used by customers to get real-time data from, say, the stock market. If you use them directly in your workbooks to get real-time data directly in Excel, and see it noticeably take time in seconds or minutes or more, chances are you'll now see very noticeable improvement in the speed at which you see results, because we’ve sped-up RealTimeData (RTD) function  by many times.

We achieved this speed-up in calculating real-time data by removing bottlenecks in its underlying memory and data structures as well as making it thread-safe to allow it to be calculated on all available threads of Multithreaded recalculation (MTR). An after effect of making it thread-safe is that MTR doesn't need to be paused to run RTD function anymore, which improves performance noticeably when calculating it along with lots of other calculations.

 

But Wait! There's more... we have improved upon multiple use cases we heard being slow, or freezing, or not responsive involving file open, paste, insert, delete, VBA, sharing Excel in Teams, and others...

1. Faster opening workbooks with many user defined functions (UDFs).  You may have noticed opening workbooks with many UDFs including those from Add-ins might be slow. We had an inefficient linear scan search to lookup every UDF found in the workbook. We have a more efficient cache and search to make this lookup much faster now.
2. Faster large paste operations using clipboard. If you saw noticeable slowness pasting large content like HTML, Images, Objects etc. into Excel, you should now see this much faster. We have made the underlying content streaming data structure more efficient and responsive.
3. Speeding up deleting ranges that include merged cells. In the case of merge cells, we found that we were inefficiently iterating through the rows in the ranges multiple times before deleting. We are now more optimal in doing this.
4. Faster inserting a column in a sheet when rows are filtered. We found that this happened especially when the sheet has thick borders and evaluating borders thickness is an expensive operation. Also, we found we did this incorrectly for hidden or filtered rows as well. We do not do this now, thus speeding up the operation.
5. Faster recalculating user defined functions (UDFs) in VBA. When VBA UDFs are calculated, they update the VBA Editor’s state (title bar, output window, etc). This is very expensive and worse, this slow performance continued even after closing the Editor window.  We’ve now fixed this where closing the VBA Editor window removes the overhead.
6. Better sharing of Excel Application Window from Teams. You may have noticed that after sharing Excel Application Window from Teams and working in it, it may start to freeze. We found Excel to leak resources causing this to happen. We do not leak these resources and have eliminated this freeze.
7. Faster operations in worksheets with large number of formulas spanning a range of cells. When performing some operations (for example “Convert to Number�) we were needlessly iterating over structures for frequently used formulas within the workbook. We no longer perform this unnecessary work.
8. Faster “Check For Errors� on large sheets with many errors. We found that when a table contains 10s of thousands of rows as well as empty cells, we generated an inefficient data structure of every error and for each iteration for an error. In the worst case, Excel would run for very long periods of time or hang, consuming high CPU and memory. We now have an efficient data structure created and used once, returning results in seconds when it would take many minutes if not hours.
9. Faster invoking ALT+ shortcut key functionality in Excel. There used to be a slight delay when using ALT+ shortcuts.  This was caused by the wait to show the Tooltip windows.  This is unnecessary for expert users.  Now Excel no longer waits for the Tooltip windows allowing expert users to type shortcuts much faster.
10. Faster international string comparisons.  String comparisons are common within Excel.  International string comparisons have been sped up by optimizing the code that performs the comparisons.

Microsoft 365 can help you be even more productive¹. Give it a try today!

 

Refer to the following for more Excel Performance Tips. We’re going to continue our team’s work with more performance fixes in other areas, but we would really like to hear from you to ensure our fixes align closely with the freezing, or slow, or not responding issues you experienced... and so, please stay connected to Excel Tech Community, read Excel blog posts, Send us a Smile or Frown, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.

 

Thanx,

Prash Shirolkar

Program Manager, Excel

 

¹ All available in Monthly channel in versions 2007 and later. Semi-annual channel preview in version 2008 and later.

Tue, 22 Sep 2020 09:42:40 PDT