Tag: Productivity

[Free Excel Course] Lesson 26 – Introduction to Macros in Excel

Posted on by Annie D

In this video, I will give you a quick introduction to Excel macros and the VB Editor.

By recording a macro in Excel, you can easily automate a lot of tasks. And the best part is that you don’t need to know any coding or VBA to do this.

When you record a macro, Excel simply follows whatever you do in the workbook and create a code for it. Now you can run this code and Excel will automatically follow all the steps you specified.

This video lesson covers the following VBA topics:

— How to record a macro in Excel
— Absolute and Relative Cell References
— Introduction to the Visual Basic Editor.

This video is a part of my Free Online Excel Training where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

FREE Online Excel Training – Video Course for Beginners


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

Running Total in Column, Power Query, DAX or Worksheet Formula? Excel Magic Trick 1553

Posted on by Annie D

Download Excel Start File: http://bit.ly/2XGiJDe
Download Excel Finished File: http://bit.ly/2wKf0c0
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to create a Running Total (Cumulative Totals) in a Column using Excel Worksheet Formulas, Power Query M Code or a DAX Calculated Column in Power BI or Excel Power Pivot Data Model. Learn how to create Overall Running Total and Running Totals by Month. Learn the important skill of creating Expandable Ranges in a Worksheet Formula, DAX Formulas and Power Query Formulas.
Topics:
1. (00:07) Introduction
2. (01:20) Excel Worksheet Formula solution using SUMIFS and EOMONTH functions with an Expandable Range. Two formulas: Overall Running Total and Running Totals by Month.
3. (04:32) Look at expandable range in a Worksheet Formula
4. (05:18) Import Table for both Power Query Solution and DAX Solution using Power Query and the Reference feature.
5. (06:50) Power Query Custom Column with Table.AddColumn Function, Table.SelectRows Function and Two M Code Custom Functions to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column.
6. (08:15) Lesson about how Custom Columns and M Code Custom Functions work, including the how the keyword “each” is a substitute for an explicitly defined Custom Function.
7. (13:33) Look at expandable range in a Power Query Formula.
8. (14:25) Field Access Operator for Looking up: 1) A Full Column or 2) A Single Item from Each Row.
9. (20:01) DAX Calculated Column with the functions CALCULATE, SUM, FILTER, EARLIER, EOMONTH and a Var Variable to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column.
10. (26:46) How to use a Variable in DAX. See Var = Mon Return syntax for a Variable in DAX.
11. (30:26) Summary

View on YouTube

DAX Fridays! #130: DAX 101, the basics of the DAX Language

Posted on by Annie D

In today’s video I want to go through the basics of DAX and explain basic pitfalls that you will fall into if you come from excel or are a business user.
#daxfridays #dax #curbal

Have a great Friday!

Calculated column vs measure video: https://www.youtube.com/watch?v=SmXLgEHXSGc

Vertipag (DAX engine inner workings) video:

Curbal DAX glossary: http://bit.ly/2I3fPn2

Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU

Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#daxfridays #curbal #dax #powerbi #mvp

View on YouTube

Surprising: Split Outlook Distribution List In Excel – 2282

Posted on by Annie D

Kim has a distribution list in Outlook with 130+ names. She wants those names in Excel. When she copies and pastes, everything ends up in a single cell.

In this episode, a simple set of steps to split the distribution list into rows and then columns for Name and E-Mail address.

These steps won’t (yet) work on a Mac. You are using the Get & Transform tools (also known as Power Query) on the Windows version of Excel.

View on YouTube

Scenario Analysis Patterns in Power BI using DAX

Posted on by Annie D

Gain access to resource download by registering here – http://bit.ly/2EV1hne

What’s it all about? We’ll be reviewing a number of techniques specifically around scenarios analysis in Power BI. Don’t just think Power BI is just a visualization tool, it is a powerful analytical engine that can produce amazing insights including running custom scenarios on your raw data.

***** Learning Power BI? *****

All Enterprise DNA TV Resources – http://bit.ly/2ZhmVtV…

FREE COURSE – Ultimate Beginners Guide To Power BI – http://bit.ly/2ESTLcy…

FREE COURSE – Ultimate Beginners Guide To DAX – http://bit.ly/2ESTLcy…

FREE – Power BI Resources – http://bit.ly/2ZfnaWh…

Learn more about Enterprise DNA – http://bit.ly/2RacRD3

View on YouTube

Document all Power Query M code without copy pasting it

Posted on by Annie D

In todays video I am going to show you a trick that Chris Webb shared on his blog recently: how to document all M code using Excel.
#powerbi #curbal #powerquery

To be able to properly follow along, make sure you check out Monday’s video:

Chris Webb blog post:
http://bit.ly/2PTO3vo

Other videos on documenting power bi files

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

View on YouTube

Quickly Add Bullets in Excel – 2281

Posted on by Annie D

I was trading Excel tips with Robert from Florida and he gave me a list of four hard ways to get bullets into Excel. There is now a fifth way and it is super-easy! At least, it is easy if you are using Excel on a Windows machine and you have a numeric keypad on your keyboard.

View on YouTube

Connect directly to Power BI service and analyze that data in Excel

Posted on by Annie D

Did you know that you connect to a dataset in the Power BI service using Excel as you do today with Power BI Desktop? No? Then watch this video, it is going to blow you away!!

#powerbi #excel #curbal

Here are the links you need to get this started!!
http://bit.ly/2H9sUdH
http://bit.ly/1RJgKWK

XMLA video: (more to come)

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#POWERBITIPS #CURBAL #POWERBI #MVP

View on YouTube

[Free Excel Course] Lesson 25 – Protecting Worksheets / Workbooks in Excel

Posted on by Annie D

In this video of this Free training, I will show you how you can lock cells or protect worksheets and workbooks in Excel.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can protect cells, worksheets, and workbooks in Excel with a password (or without one). Once a worksheet is protected, you’ll need to enter the password to access it (in case a password has been applied).

Note that Excel in-built security feature is not very strong and you can easily open worksheets/workbooks that have been locked. However, if you want to quickly protect it before sending it to your manager/client (so they don’t end up changing anything by mistake), it works well.

This video is a part of my Free Online Excel Training where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

FREE Online Excel Training – Video Course for Beginners


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

MSPTDA 28: Build Power Query Bridge Table in Power BI & Power Pivot for Many To Many Relationship

Posted on by Annie D

Download files for videos: http://bit.ly/2KhPz9Z
Class web site with individual file downloads: http://bit.ly/2GF25Ni
pdf notes for Video: http://bit.ly/2KicfXm

In this video learn about how to create a Bridge Table for a Many-To-Many Relationship using Power Query in either Power BI or Excel Power Pivot. Learn three methods for a Bridge Table: B-Directional Filetrs, CROSSFILTER DAX Function or a Table Filter.
Topics:
1. (00:23) Introduction & look at Data Modeling Problem
2. (01:00) Fundamental Problem with a Many-To-Many Relationship
3. (02:05) What is a Many-To-Many Relationship?
4. (03:25) Start to create Reports and discover the Problem with a Many-To-Many Relationship.
5. (04:52) Build Bridge Table using Excel Power Query
6. (07:32) First look at underlying filtering for a Many-To-Many Relationships
7. (08:36) Three Options with a Bridge Table.
8. (09:02) Build Bridge Table using Power BI Desktop Power Query
9. (10:25) Bi-Directional Filter
10. (10:53) Visual Animation to illustrate how Many-To-Many Relationship works with a Bridge Table.
11. (11:39) Ambiguity in Model with Bi-Directional Filters.
12. (12:19) CROSSFILTER DAX Function example
13. (13:41) Table Filter Example
14. (14:05) Expanded Diagram to “see” how Table Filter works with a Bridge Table and a Many-To-Many Relationships
15. (15:08) Difference between Grand Total Cell formulas for CROSSFILTER and Table Filter
16. (16:10) Cross Tab Report
17. (16:53) Conclusion
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

MSPTDA 27: SUMPRODUCT & SUMIFS Worksheet Array Formula to Add Units in Many To Many Relationships

Posted on by Annie D

Download files for videos: http://bit.ly/2KhPz9Z
Class web site with individual file downloads: http://bit.ly/2GF25Ni
pdf notes for Video: http://bit.ly/2KicfXm

In this video learn about how add total units from a Fact Table with Book ID. See two formulas: Total Units for each Book ID, but then also Total Units for Each Author when each book may have multiple Authors, in a Many-To-Many Relationship. Learn about the Plus Math Operator for OR Logical Test. See the IF Function to lookup correct Book IDs based on OR Logical Test.
Topics:
1. (00:15) Introduction
2. (01:12) Many-To-Many Relationships. What is a Many To Many Relationship?
3. (02:34) Worksheet Formulas and caveats
4. (03:22) SUMIFS, INDEX and MATCH Worksheet Functions to create a Total Units for Book ID Report.
5. (06:01) SUMIFS, INDEX, MATCH, IF and SUMIFS Worksheet Functions in an OR Logical Test Calculation to help simulate a Many-To-Many Relationship with the goal of creating to create a Total Units for each Author Report.
6. (08:05) OR Logical Test
7. (12:52) Conclusion
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

DAX Fridays! #129: New functionality for ALLSELECTED

Posted on by Annie D

In the Power BI desktop update for May 2019, the ALLSELECTED fucntion functionality has been updated and in this video we discuss the changes.

The previous functionality was discussed here:
http://bit.ly/2KeNcEQ

Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU

Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT

Here you can download all the pbix files: http://bit.ly/2PUURaz

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

http://bit.ly/2V3o0Ux

Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://amzn.to/2YRfIBm

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#daxfridays #curbal #dax #powerbi #mvp

View on YouTube