A List of Actual Work Use Cases to Get You Thinking!
Data and data tools seem to be THE hot topic in the business world today. Accounting and finance folks work with data a lot on a daily basis, yet only a minority have heard of or utilized PowerQuery in #excel or #powerbi (that’s my experience from speaking and consulting, and Wyn Hopkins mentions this occasionally in his excellent posts). Tip — follow Wyn on LinkedIn if you don’t already!
I get the question “what does PowerQuery actually do?” My standard response is “it makes work evaporate”.
Somehow, that isn’t specific enough for many people. 🤔
So, from examining how PowerQuery…
Every little time-saver helps…and this is a good one!
With the introduction of iOS 14, Apple introduced a new accessibility feature. You can enable certain actions to occur when you double-tap or triple-tap the back of the iPhone. 😳🔥
I had not activated this option since I use a case on my phone. This makes no difference (thanks for the heads up Jonathan Mattson ).
Here’s how it’s done:
👉 Open Settings >> Accessibility >> Touch
👉 Scroll to the bottom and activate Back-Tap to On (the default setting is Off)
👉 Choose Double Tap and select an action (there are many choices — choose the action that you…
We All Need to Do It (But, Generally Don’t)
The Problem
When was the last time you cleaned up your PC or network drive of folders and files on your computer? Probably never, if you are like most professionals I know.
Why? It’s not easy.
Let’s change that!
Solving the Problem
I’m coordinating a data cleanup project with a client and the effort involves cleaning up a network directory that includes 700+ folders and approximately 14,000 files. 😫
Think about how you would tackle this task and you quickly realize two things.
✔ No one does because it’s not simple to do and rarely becomes a priority. …
Coming to Microsoft 365 (I Hope) Soon!
A new function introduced on December 3, 2020 by the Excel team at Microsoft is getting rave reviews. 🔥
And it appears warranted.
It is essentially VBA (Visual Basic) without needing Macros to create User-Defined Functions (customized calculations) within plain Excel (.xlsx) files.
Review the content below to start to get a sense of why this is a big deal!
“Microsoft has just announced a brand new function for Excel (Office 365 Beta / Insider version) — LAMBDA.
The power of this latest addition to the Excel family of functions cannot be overstated. This is going to REVOLUTIONISE how you…
Part 2 — Building Basic Data Analysis with Excel Power Tools
Overview
Continuing from Part 1 — “Use #PowerQuery to IMPORT .csv formatted data from the web”
Once I have extracted data (a list of failed banking institutions), it needs to be transformed in order to simplify analysis.
In this case, a table of date dimensions would prove very useful. I have a closing date for every record.
A date dimension table that would associate Year, Quarter, Month, Week of Year and Day of Week greatly simplifies slicing and analyzing the data for different views. 💯💪
I highlight one of the methods I use to create the date dimension table in video…
Part 1 — Building Basic Data Analysis with Excel Power Tools
Overview
When getting data from the web, a lot of sites provide the ability to DOWNLOAD a csv formatted file of the data.
Rather than download it though, you can simply connect to it and import the data using PowerQuery.
Why import? Because the data can be refreshed and updated anytime the underlying source on the web changes!
That’s convenient!
One of my favorite small datasets that I use for training is a listing of banks that have failed (closed or been acquired).
In this video, I walk through importing the data from the web via connecting to the csv rather…
Use of the INDIRECT and SEARCH functions can turbocharge your Conditional formatting in Excel!
INTRODUCTION
Conditional formatting is a great way to highlight items for the spreadsheet user. But, setting the conditions up can be tricky.
It’s even more confusing when you introduce variables into the formatting (so that user can define what to format).
But, persevere and the payoff is significant!
Two of my favorite techniques when working with lists of data are to highlight entire rows in tables when specific values are found in certain selected table columns (e.g., highlight all rows where company market cap exceeds a certain value).
HOW?
I use two Excel functions which make this happen — INDIRECT and SEARCH…
Learn to reduce reliance on the Ribbon — You are on your way!
Hide the Ribbon Commands menu in #Excel (or any MS Office program) to free up extra screen space! #twinztalk #twinztechtip
If you use Excel frequently and access commands via the Ribbon, you are taking the SLOW path AND losing precious workspace to consistently display the Ribbon.
Hide it and only display it when you do need it!
And you will need it less and less if you adopt the following behavior changes:
1️⃣ Use basic keyboard shortcuts.
No need to get crazy — see Tip #9 — https://lnkd.in/ehygTYj
2️⃣ Take advantage of…
Natively built into Gmail — desktop and mobile apps — and it’s awesome!
Gmail user?
You can now schedule your emails to be sent (built natively into Gmail)! #twinztalk #twinztechtip
Wow.
This is a recently added capability and a reason I will start to use to Gmail more on my mobile devices. 🏋️♀️💯
Here’s how simple it is:
Compose email and click “Send” dropdown arrow
- “Schedule send” will show as an option.
Schedule when to Send. Done.
#TwinzTechTip #TwinzTalk
A frequent task in business is to compare two lists of data.
Have a master customer, product, mailing list etc and need to compare it to another list to see what is missing and needs to be added? Typical scenarios…
Basic Power Query makes this easy!
Please don’t do this by hand. 😳
Common approaches include using Excel MATCH or VLOOKUP function (which generates errors which indicate items aren’t on one list).
However, power query makes those methods tedious by comparison!
Here are the steps to using Power Query:
1️⃣ Create a query from the MASTER list. “Connection Only” query.
…
Don Tomoff
It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics
