Learn Advanced Excel in 3 Months

Roadmap with Resources

• Chandoo
• January 30, 2023

Who is this roadmap for?

This roadmap is for anyone interested in learning Advanced Excel Skills for Data Analysis or Reporting work. Follow this roadmap if you:

• Want to learn "Advanced Excel"
• Can allocate 4-8 hours every week for 12 weeks
• Have basic understanding of Excel
• Are motivated and driven

Month # 1

Focus on getting & cleaning data

This month focus on how to get, clean & manage data within Excel. The key learning objectives will be how to use Power Query, Tables, Data management features and how to perform simple "Exploratory Data Analysis" with Excel.

Technical Skills

• How to get in and use Excel?
• How to use Excel tables?
• How to use Power Query to load and clean data?
• Working with Flashfill, text to columns and other clean-up functions in Excel (video)
• How to filter & sort data in Excel?
• Doing simple descriptive analysis of data with in Excel
• How to use Excel References (video)

Course

If you need step-by-step course with clear examples, sample files and downloadable materials to teach you advanced Excel & dashboard reporting, please check out my Excel School program.

This comprehensive online class will teach you everything mentioned in this roadmap + more.

 

Click here to know more.

Domain Skills

In Month 1, learn about your industry / domain by:

• Talking to your friends who work in the same domain
• Visiting company websites and understanding their business model and nature of work.
• Read books on Statistics and data analysis.

Project

In Month 1, You can do a simple project to implement all the skills you have learned.

1. Build an Expense Tracker in Excel that loads data from your bank statements (in PDF or CSV or Excel formats), cleans the data and tells you where your money is going.
2. Run a survey about anything on your friends and analyze the data. Use tools like Microsoft Forms or Google Forms to automate the data collection and Power Query to consolidate the data.
3. Download any sports / movies or other favorite topic data and cleanse & analyze it. See if you can find anything odd in the data. Use websites like kaggle to get the data.

Recommended book: Data Analysis & Business Modeling by Wayne Winston

Month # 2

Focus on analyzing the data

This month learn how to analyze the data by asking right questions, using formulas + pivot tables to answer them. Learn how to highlight important points and information with conditional formatting and how to make interactive analysis with slicers & data validation.

Technical Skills

• Formulas:
  • Top 10 Excel Formulas for Data Analysis
  • SUMIFS
  • XLOOKUP
  • FILTER
  • SUBTOTAL
  • IFERROR
• Pivot tables:
  • Introduction to Pivot tables (video)
  • Advanced Pivot Table tips & tricks
  • How to use Data Model & Relationship feature of Excel
  • Introduction to Power Pivot
  • More on Pivot Tables
• Concepts:
  • Average vs. Median vs. Quartiles
  • Data & BI Terminology

Domain Skills

In Month 2, learn about your industry / domain by:

• Studying necessary statistical and functional areas of your work.
• Talk to your friends in the industry. Take them out to coffee and ask them what they do, what kind of analysis they often do and how they do it?
• Find volunteer / freelance / extra work opportunities to practice what you are learning. 
• Next time you meet your friends, discuss / analyze your favorite sports team / tv show / politics / weather / video game. 
• Read books or watch videos about your domain. 

Project

In Month 2, You can do a simple project to implement all the skills you have learned.

1. Sales Report from awesome chocolates dataset. Use this dataset and generate a sales report to show how an individual salesperson has done in 2021. Show their sales & boxes by product. Make it interactive so we can select another person easily. Build this with formulas alone and then with Pivot + formulas.
2. Build a financial goal tracker. You can input any number of goals and their cost (in today’s terms) and the tracker tells you how much you should save to have the money in future (with inflation, various return rates).

Month # 3

Focus on presenting the data

This month build skills to present your data. Understand how various charts work, how to create and customize them, how to make them interactive and how to lay them out. Learn when to "not" use charts. Build verbal and written communication skills to share your insights with others.

Technical Skills

• Charting:
  • Select right chart type for your data
  • Rules for making column charts in Excel (video)
  • How to make a budget vs. actual chart in Excel
  • Create a basic interactive chart in Excel
  • How to use slicers in Excel?
• Dashboards
  • What is a dashboard and how to create one?
  • KPI Dashboard in Excel
  • Project Management Dashboard with Excel
  • 200+ Dashboards – Examples, templates
• Concepts:
  • How to use Data Validation in Excel
  • Essential Excel Keyboard Shortcuts
  • How to use VBA
  • How to use Power BI

Additional Resources:

1. Excel Basics page on this website
2. Advanced Excel Skills page on this website
3. Learn statistics & probability using Excel
4. How to build a simple business model in Excel

Domain Skills

In Month 3, learn about your industry / domain by:

• Thinking about how the technical concepts relate to domain concepts you are learning.
• Imagine work scenarios and see what tech, domain skills are needed to solve them. (For ex, you want to know why shipments are delayed in first quarter of the year, what domain knowledge is needed and what tech skills are needed to figure the reason?)
• Think about the human side of analysis. How people read & consume information etc.
• Learn more about your area of work and what will make you successful.

Recommended book: Storytelling with data by Cole N Knaflic

Project

In Month 3, here are a few projects you can try:

1. Build a one page dashboard for Awesome Chocolate CEO with Excel Use this dataset
2. Build a system to automate data collection, clean-up, analysis and visualization for your industry
3. Make Expense Tracker 2.0 that can read a folder of monthly debit & credit files, show where the money is coming from and where it goes, spot trends and does forecasting.

All 3 Months
in One Course

Introducing Excel School

Learn all the Advanced Excel Skills in one place with my Excel School Program. Rated 5 stars by more than 10,000 students, this course will give you everything you need to be successful in workplace.

Check out the course and enroll today

Got Questions?

Share your questions and comments below...

Got a question about this roadmap or have suggestions? Please share your thoughts in the comments section below.

The post Learn Advanced Excel – 3 Month Roadmap with Resources appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Background

I have always had a fascination with Mandelbrots, since I was introduced to them in High School in the late 1970’s. With the advent of personnel computers, I could then program my own Mandelbrot plots in Quick Basic, before some of the highly optimised Fractal Programs popped up in the mid 80’s, my favorite at the time was Fractint.

I wrote my first Excel Mandelbrot program in the late 1990’s, using VBA. But it was in a business meeting in the early 2000’s where I had questioned the need to get develop and application, where I questioned “Why do that, when we could easily do that in Excel”. The salesman gave me a spiel about why you couldn’t do that do that in Excel, with me retorting “You can do anything in Excel”. He immediately responded “You can’t make a Mandelbrot in Excel without using VBA”.

Challenge accepted !

That night over dinner, I had a light bulb moment and 20 minutes later the proof of how to develop a Mandelbrot in Excel was defined and working. I was recognized for that effort by John Walkenbach in his 2007 Excel Bible.

What is a Mandelbrot?

A Mandelbrot is a contour plot of the count of the escape iterations of the formula Z²=Z²+1

Areas shaded black, the iterations never escape a predefined value, areas of similar color outside the black area, take a similar number of iterations, before exceeding that predefined value, ie: escaping the Mandelbrot. Mandelbrots are fascinating constructs with many one remarkable feature of the Mandelbrot being that it has a defined area, but an infinite length boundary.

The Mandelbrot is calculated by solving the equation Z²=Z²+1

Z is calculated via a formula that is recursive, ie: it feeds on its previous values.
This value of Z is equal to a previous value of Z via some function, in this case Z^_new2=Z^_old2+1

Z is also a complex number, which is typically shown as Z = X + Yi, where i is the square root of -1

The Mandelbrot equation can be solved in the real / orthogonal plain using X & Y values iteratively.

My first post at Chandoo.org in 2009 was a discussion of how to use the Excel Data Table function which included a section on Iterative Functions and Fractals. https://chandoo.org/wp/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/.

Calculating a Mandelbrot

The Mandelbrot is the calculation of a solution to the equation, Z²=Z²+1.

This can be performed on the orthogonal X-Y plane, where the X & Y values form the starting location for a solution, which is then iterated through the formula, keeping count of the number of iterations to achieve or exceed a predefined limit.

You can read about the math required in the Computer Drawings section of https://en.wikipedia.org/wiki/Mandelbrot_set

You can follow along using the Mandelbrot file using the link below

Effectively you need to have 3 loops inside each other, which can be done within Excel with a bit of thinking and smart use of Excel Functions.

We can use the Rows of a worksheet to do the iterations of one set of starting X & Y values. The other two loops that feed the initial X and Y values are souced iteratively using a Data Table function. that will be described in a section below

This is shown below:

1 – Starting coordinates, X & Y values, these will be updated via a 2 dimensional Data Table

2 – Transfer starting values into the iteration area

3 – The starting values become the old X & Y Values

4 – Calculate the new X & Y values using the formula

Xnew = Xold² – Yold² +Xorig

Ynew = 2 x Xold x Yold +Yorig

5 – Calculate the Rsq value, or the distance of the new point from the origin

Rsq = Sqrt( Xnew² + Ynew² )

6 – If the RSq value (7) is not greater than the Escape Value (8) repeat steps 3, 4 & 5 above

7 – When the Rsq value (7) is greater than the Escape value (8) stop iterations and determine the count in this example it is 8

So we have determined the Count value 8 for our starting position of x=-1.0, y=0.4

you can examine the table of Xold and Yold values above and see how they change over the various iterations.

Once the Rsq value is greater than 4, subsequent iterations can never be less than 4 and the iterations have escaped the Mandelbrot, in this case in 8 steps.

This is shown graphically

We now need to do that for each set of x, y coordinates in the range x = -2 to 1, y= -1.5 to 1.5

To achieve this we can setup a 2 Dimensional Table, and then use the Excel, Data Table function to feed the results into the Xorig and Yorig positions.

This is shown below

1 – A 2 dimensional data area, yellow, is setup. the column headers contain the x coordinates from -2 to 1 and the row headers contain the y coordinates from -1 to 1.

The Excel Data Table function is used to put the Column & Row headers in order into the Xorig and Yorig positions

ie: The Data Table is setup using the Data, What If Analysis, Data Table menu and entering the cell references for the Xorig and Yorig cells

2 – The Data Table function iterates through every cell within the Data Table, Yellow, area sending the Row and Column headers values to the Xorig and Yorig cells $F$10 and $F$11 respectively

3 – The Data Table function recalculates the worksheet and sends the Count, 2 in this case, back to the Data Table Upper Left cell H222.

4 – The Data Table function then stores that value in the Data Table position at the intersection of the Column and Row ie: Cell J225

The data Table function continues iterating through each of the yellow cells, repeating steps 1 to 4 above.

Plotting the Mandelbrot

Once the Data Table function has completed iterating through the Mandelbrot data table area you can zoom in on parts of the table and can see they contain the Counts of the escape iterations as defined above

You can start to visualise the shape of the contours of the Count by following areas of constant values

However Excel has some charts that are suitable for displaying contour data, the Surface, Contour chart type.

Complex Math Mandelbrot

The Mandelbrot calculated and displayed above conforms to the maths described in the Computer drawings section of the https://en.wikipedia.org/wiki/Mandelbrot_set post. But I had always wondered whether Excel could calculate the Mandelbrot directly using complex maths.

Excel contains a number of native functions that handle complex numbers, ie:

You can read more about the Excel use of complex numbers at:

COMPLEX() – Converts the component values of a complex number to a complex number

IMSUM()– Sums or adds up complex numbers

IMPOWER() – Calculates the complex number raised to some power

IMABS() – Calculates the absolute value of an complex number, ie the square root of the complex numbers components squared

https://best-excel-tutorial.com/59-tips-and-tricks/213-complex-numbers

I had always wondered whether we could use the Complex math functions within Excel to calculate the Mandelbrot directly.

Well wonder no more, the answer is Yes it can.

You can follow along using the Complex Mandelbrot file from the link below

By using the Excel Complex functions, the math is simplified at the expense of speed.

The Excel complex functions are incredibly slow compared to the native excel functions which in most parts are highly optimised for speed.

Lets examine how this Mandelbrot works using the Excel’s complex functions

The first thing to note is that we now do not require the Xold, Xnew, Yold and Ynew columns

The data is all contained within the z column, and each row uses the previous row for it’s iteration calculations.

Using the same inputs as above x = -1.0 and y = 0.4, these are stored in G12:G13 respectively.

Cell E18 converts the Xorig and Yorig values into a complex number using the formula:

E18: =COMPLEX(G12, G13)

Excel displays complex numbers as a text string, ie: Cell E18: displays -1+0.4i representative of the complex format of -1x +0.4y starting position of the selected Mandelbrot cell

The values in the range E18:E200 are complex numbers, but Excel displays them as a text string and stores the complex number components in the background.

E19: calculates the sum of the power of the previous number and adds it to the original number using the formula:

E19: =IMSUM( IMPOWER(E18, $G$7), $E$18)

This is using IMSUM() to add the result of the squaring of the previous complex number E18 and the starting value, also in E18.

The squaring is done using IMPOWER(E18,$G$7), where E18 is the number being squared and $G$7 has the value 2 in it

E20: similarily has

E20: =IMSUM( IMPOWER(E19, $G$7), $E$18)

This is using IMSUM() to add the result of the squaring of the previous complex number E19 IMPOWER(E19, $G$7) and the starting value, in E18.

This is repeated below for the iterations

The Rsq value is calculated using the formula in F18

F18: Checks that the Absolute value of the complex number is not greater than the Iteration Escape value using the function

F18: =IF(IMABS(E18) > $G$9, 0, IMABS(E18))

In this case we can use the complex function IMABS() to calculate the Rsq value and check that against our escape iteration limit from Cell $G$9.

The rest of the calculations and function of the Mandelbrot are the same as the original version described above.

The final result is a Mandelbrot Chart shown:

Zooming

You can manually zoom in on areas of the Mandelbrot by changing the starting coordinates

It isn’t difficult to see that this can be updated by using a simple macro and the Double Click event, where a user can double click on a cell within the Mandelbrot, and the macro transfers these coordinates into the cells above using a 10:1 or other predefined zoom factor

Conclusion

I hope you have enjoyed this introduction to iterative functions within the Excel worksheet and a foray into complex numbers

I have demonstrated that Excel can compute and display a Mandelbrot or in fact any iterative function without the use of Visual Basic on the Excel worksheet.

Excel has tools that allow multi-dimensional iterations within the worksheet space. This opens up the use of Excel for complex financial and other modelling analysis in multiple dimensions.

Excel can do all this using Real or Complex numbers.

Have you used complex numbers or iterative functions in Excel ? Let us know in the comments below.

The post The Excel Mandelbrot appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Here is a fabulous New Year gift to you. A free 2023 Calendar & Activity planner made entirely in Excel. This dynamic calendar workbook lets you:

• See the calendar for entire year in a single view
• Dynamic any month calendar with detailed plan view
• Automatic updation of holidays, weekends and activities
• Snapshot of upcoming activities
• Fully customizable – start on any day, any weekend, custom holidays
• Optimized for screen and print outs

Click the below button to download the free 2023 calendar template.

Compatibility

This calendar is compatible with Excel 365. It uses modern dynamic formula techniques to automatically generate the calendar, planner and month views.

If you have an older version of Excel (such as 2019 or 2016, 2013) then please use this alternative version.

How to use the 2023 Calendar Workbook?

The calendar & planner file has 4 tabs.

1. Calendar tab: See the 12-month calendar view + upcoming activities in this page. It also highlights any holidays, weekends and planned activities on the calendar in a different color.
2. Any month tab: This page lets you see the calendar for any specific month in a detailed view. You can change the month from cell C3 and the calendar updates automatically. The calendar shows date, any activities planned in a neat grid view.
3. Planner tab: Use this tab to set up your activities. Whatever items you list here will automatically show up on the calendar & any month tabs.
4. Customizations tab: Do you want to change the way your week begins? Need to add some holidays or change the icons? Use the customizations tab.

How is this calendar made?

The calendar workbook has two main components.

• Calendar
• Planner

Calendar Generation

To generate the calendar, I am using the dynamic array functionality of Excel 2023. We can use the SEQUENCE function to create all the dates in any given year.

For example, =SEQUENCE(365,,DATE(2023,1,1)) generates all the 365 dates in the year 2023.

I then used the same logic to generate monthly calendars for all the 12 months and adjusted them based on the week start option.

Once the monthly calendars are generated, then I highlighted the weekends, holidays and activities using conditional formatting.

Activity Planner

You can set up any number of activities in the planner table. I am then using FILTER function to filter out the activities for a given day and show them next to the calendar date.

Also, if “highlight activities” is enabled, then I am highlighting the calendar cells in a different color.

In the 12-month calendar view, I am showing upcoming 10 activities using FILTER function too.

Know more about these calculations

If you want to learn more about the calculations and set up of this workbook, please refer to these articles + videos.

• Dynamic array functions in Excel – introduction to FILTER, SEQUENCE etc.
• Conditional formatting to highlight values in Excel
• Data validation to select a month in Excel cell
• Excel tables – How to create a simple tracker in Excel
• Video: How to use FILTER function in Excel

The post FREE Calendar & Planner Excel Template for 2023 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Hi everyone…

Let me start by wishing you all a very happy holiday season and stellar start to 2023. Here is our annual holiday card.

[pictured left to right: Nishanth (son), Jo (wife), Nakshu (daughter) and Chandoo (me)]

Thank you…

2022 has been a phenomenal year for me both personally and professionally. It brought so much positivity, energy and love. I want to take a moment to thank some of the key people who inspired, helped or contributed to my mission this year.

• Thank you: for reading, watching, purchasing or listening to my work. You continue to inspire me and energize me.
• My family: for being the absolute bedrock of my support. You always encourage me to experiment, have fun and be myself. Thank you
• Our staff: Thanks to Ravindra, Pothi & Soumita for all the AWESOME work you have done this year.
• Our forum members: You selflessly answer questions and help people do great things at their work. Thank you.
• Vena: for working with me this year on various events and sponsoring my channel. Special thanks to Zach and Siobhan for all the awesome work you have done.
• Global Excel Summit: for inviting me again in 2022 to speak and share some of my knowledge with your members. You also taught me many things. Thanks to Tea Kuseva for your tireless work behing scenes.
• Bulgaria Excel Days: for having me at the BEST conference ever. You have inspired me to work hard and party harder. Thank you Boriana & team for the amazing experience.
• Mentors & Teachers: This year I learned from 100s of people thru platforms like YouTube & Podcasts. Some of the key individuals that shaped my thought and action this year are,
  • Adam Saxton from Guyinacube
  • Wheezy Waiter, Ali Abdaal, Simone Giertz, Nikhil (CaptainSinbad) Govind Vasanta and Rajesh Vaidhya
  • Oz du Soliel, Jon Peltier, Gasper Kamensek, Ken Puls & Reza Rad
• Sponsors: This year I had the fortune of working with some amazing brands & companies. I want to shout out to BrightData, Vena and ZebraBI for the love and support you have shown.
• Companies:
  • Big thanks to Microsoft for making such awesome software – Excel & Power BI
  • YouTube for making a platform thru which I share and connect with millions around the world
  • WordPress for coolest blogging platform
  • Elementor, Wishlist, e-junkie, stripe, convertkit, epidemicsound, unsplash, pexels, mediavine and aws for helping me run my little kingdom without hassles
  • Camtasia & Adobe for your marvelous software that helps me create videos for my audience
• Philip Wiest for answering student questions during my Power BI Day event.

This holidays…

We (Jo, kids & I) are traveling to India for the Christmas & New Year break. I will not be posting any new videos or articles until 1st week of January. If you send me an email or leave a comment I will not be replying to it until much later. Stay safe and have fun. See you in 2023.

The post Merry Christmas & Happy New Year 2023 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Here is a really tricky problem. Recently I was given a data set like this (shown below) and asked to find the position of lookup value in the list. The only glitch is that, instead of values, the lookup table contained lower and upper boundaries of the values. See the below illustration to understand what I mean. In simple words, we have to find the range that has the lookup value. Now, the problem is similar to between formula trick we discussed a few days back, yet very different. We all know that,

• XLOOKUP formula looks up a value in a table and returns the corresponding value in next column
• MATCH formula looks up a value and tells the position of it in a list

But neither seem to solve this problem. So I naturally turned to a cup of home brewed coffee (remember, I no longer work in a office, so I can’t rush to espresso machine) and stared long and hard out of the window (remember, I no longer go to office, that means I can sit in front of a window and work). Added in December, 2022:

We can use XMATCH:

Since we just want to know which row will contain the value, we can use XMATCH as shown below.

				
					=XMATCH(1, (B6:B15<=C3)*(C6:C15>=C3))
				
			

Ok, go ahead, I will give you a minute to soak in the awesomeness of that formula.

Are you back?, well, lets explore what this formula does.

How this works:

• C3 contains our lookup value
• B6:B15 has the lower boundary
• C6:C15 has the higher boundary
• The (B6:B15<=C3)*(C6:C15>=C3) returns a bunch of 1s or 0s. It will be 1 whenever C3 is between column B&C values and 0 otherwise.
• XMATCH will match the first 1, ie the first row that matches the range.

Or even the SUMPRODUCT

Then I thought, “may be SUMPRODUCT formula would work for situations like these?!?”

After playing for a while, I got the perfect formula for this.

• Assuming the value to be looked up is in cell C3
• The start and end values are in B6:B15 and C6:C15 respectively,

We write,

				
					=SUMPRODUCT((B6:B15<=C3)*(C6:C15>=C3),ROW(B6:B15))-5
				
			

There are 3 portions in that formula,

1. (B6:B15<=C3)*(C6:C15>=C3) part: This is checking the range B6:B15 and C6:C15 to find that one set of start and end values that would contain the value in C3. The output would be a bunch of 0s with probably a single 1
2. ROW(B6:B15) part: This just gives running numbers from 6 to 15. When you SUMPRODUCT this with above you get a single number corresponding the row in which the match occurred
3. -5 part: We reduce the output value by 5 since our value began in row 6, not row 1.

Use this to lookup date ranges too:

As you can guess, you can easily use the above SUMPRODUCT formula to lookup matching date ranges too a la vlookup for date ranges.

Download Range Lookup Example Workbook:

In the download workbook, you can find both examples (values and dates). Go ahead and download it. Play with it to understand range lookup formula better.

Click here to download the sample workbook.

Do you face range lookup problem?

Often, when working on project planning, I end up checking where a date falls between given set of start and end dates. Earlier, I used helper columns to solve such a problem. But the XMATCH (or SUMPRODUCT) solution above is much more elegant and scalable. Plus it is much more fun to write.

What about you?

Do you face range lookup problem often? How do you solve it? Share your techniques and tips using comments. Thank you

More Excel Formula Magic:

• Average of top 5 values – Excel Formula Tutorial
• Count the number of unique values in a range
• Reverse a list of values using INDEX formula

The post Range Lookup in Excel – How to lookup the pricing tier? [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

You open an Excel file and some cells show #### instead of the value. Here is a quick fix.

• Just make the columns wider

Still not working? Read on…

Even after making the columns wide-enough, sometimes Excel can show ### in cells. This can happen if you have dates in the cells and some of the are negative (or after 31 December year 9,999).

See below demo:

How to fix the problem for negative dates:

Excel cannot process negative dates or dates before year 1900. In most situations, a negative date could be just a formula or data entry error. So go ahead and apply the necessary data adjustments.

Can I show another message if the cell has error?

Sure. You can use IFERROR function in Excel to show an alternative result when the original values is error. To use this:

=IFERROR(your original value or formula, “alternative result”)

For example:

=IFERROR(AVERAGEIFS(A1:A10, B1:B10, “England”), “No result”)

The above formula tries to calculate the average of A1:A10 where B1:B10 is “England”. If there is an error, it will print “No result”.

Learn more about Excel errors:

See these pages to learn more about common errors with Excel.

• Excel formulas showing as Text and not calculating
• A handy guide to common Excel errors
• Use IFERROR to stop errors in your workbook
• 10 important Excel functions you should learn

The post #### in Excel cell appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Hello folks,

Our holiday sale for 2022 is now live. Click here to purchase your favorite Excel, advanced Excel, dashboards & Power BI courses at discount.

What is this holiday sale?

Three of my most popular, awesome & valuable courses are on sale.

1. Excel School & Dashboards program – Save $50
2. Power BI course – Save $40
3. 50 Ways to Analyze data course – Save $50
4. Combo offer all three – Save $200+ 

What do you get in these courses?

In Excel School & Dashboards program, you will get

• Detailed & Step-by-step training on all day to day aspects of Excel
• Tutorials & examples to make you advanced user in Excel
• Knowledge on how to use various important formulas, how to combine them
• How to set up tables, analyze data with pivot tables & do so much more
• How to create charts, how to add interactive, dynamic features to them
• Instructions on how to create world-class dashboards, MIS reports
• Deep & clear instructions on using Power Query, Power Pivot and modern Excel features

Excel School program has more than 32 hours of video training, 50+ example workbooks & files, 1 year unlimited access so that you can become awesome at a pace that works for you.

In Power BI Program,

• How to use Power BI Desktop software
• Working with Power Query for data cleansing & ETL
• Modeling & working with business data using Power Pivot
• Creating measures and KPIs using DAX
• Setting up tooltips, bookmarks, custom interactions, drill through with Power BI
• Using and customizing Power BI visuals
• Creating stunning dashboards & reports with Power BI

In 50 ways program,

• Analyzing business data with complex Excel formulas & pivots
• Creating interactive analytics using Excel
• Statistical analysis & Forecasting with Excel
• Industry specific analytics – marketing, HR, finance & operations
• Working with data tables & monte-carlo simulations
• Clustering, KNN outlier analysis and optimization
• Using Solver
• Setting up business & financial models in Excel 

 

When is the SALE closing?

This holiday sale closes Friday mid-night – that is 9th December, 2022 mid night – Pacific Time.

Hurry up and Enjoy the sale

Go ahead and enroll in one of these courses. Become awesome in Excel / Power BI / Data.

Click here for the holiday sale page.

PS: This year alone, 366 people have enrolled in above 3 courses. You too can unlock the power of Excel + Power BI & make difference in your work. Go ahead and join us.

The post Holiday Sale is on, Save 20% on your favorite Excel / Power BI courses appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

I have been working with SharePoint a lot these days. A LOT!!!

One thing that frustrates me all the time is that there is no easy way to connect to a single SharePoint file from Power Query. Until now, that is.

The other day I learned a handy little trick from Jo (my beautiful and insanely talented wife). So here we go.

Problem: Power Query doesn’t recognize SharePoint file paths

I have a file in SharePoint (as shown below) and want to link it to Power Query (either in Excel or in Power BI).

When I use the Share > Copy Link approach I can’t get the file to open thru Power Query. You can see the Unable to Connect error message when I try this option below:

Old Solution – Long and tedious

So the normal fix for this has been,

1. Open a SharePoint Folder connection
2. In Power Query filter down to the single file I need
3. Apply any transformations
4. Finally load

New Solution – Short & Sweet

So this is the trick Jo told me.

1. Click on the ? (three dots menu) next to your file in SharePoint
2. Go to Details
3. This opens a panel on the right-hand side of the screen
4. Scroll down and locate the “path” and copy the path
5. Now use this path in Power Query (from web option)
6. BOOM! You get the data straight away.

Here is a quick demo of the solution:

Learn more about Power Query…

We can use Power Query to connect and get all sorts of data into either Excel or Power BI. If you are new to Power Query or need a proper introduction, check either of below resources.

The post How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

It is the Soccer WORLD CUP time !!! Here is an Excel Workbook to keep track of the schedule, results, group standings and more. Download it today.

Download the FREE Excel Workbooks:

Download the Excel workbook (Excel 365 version)

Don’t have Excel 365? Use the Non 365 version here.

Features of this FIFA World Cup Schedule Workbook:

Group & Knockout Stage Views

Two separate tabs provide clear view of the schedules & results from Group Stage & Knock out stage of the football worldcup. They will auto-update whenever you refresh the workbook.

Fantasy View (set your own Group Stage Winners)

Pick your own winners for each of the 48 group stage matches and then see how that pans out at the knock-out stages (bracket view) with this cool Fantasy view. Switch between Fantasy and actual standings anytime.

Match Schedules:

The workbook also provides match schedules (dates & times as per local Qatar timezone) and quick access links to the match report.

Refreshable Scores & Group Standings:

The workbook is refreshable. If you hit CTRL ALT F5 (or Data ribbon > Refresh All), the group standings, points etc. will automatically update. The workbook uses Power Query in Excel to connect to Wikipedia page and fetch standings & results.

How is this built?

I plan to make a video explaining the process if time permits. But here is the quick run-down of the process:

1. All the data comes from 2022 FIFA World Cup page on Wikipedia.
2. I am using Power Query (Data Ribbon) to fetch the relevant tables from the Wikipedia page.
3. Once the data is loaded to Excel, I created two different views, one for group stage and another for knock-out (elimination) stage.
4. Here I am using either Dynamic Array formulas (such as FILTER & SORT) or good old INDEX MATCH formulas to get the correct values.
5. In a separate tab, I have the schedule of all the 48 games. Here you can also specify the winners in a fantasy column.
6. I used Data Validation to let you (users) select whether you want to view fantasy view or actual view.
7. I applied conditional formatting to change color of the bracket view if fantasy results are shown.

Issues with the workbook? Read this:

As this workbook relies on the Wikipedia page, if something changes there, the workbook can break. I will check the file once every few days and fix any known problems. Comeback to this page and download latest version if possible.

If there are any issues with the file, please post a comment so I can fix them.

Alternative trackers:

Try the Spreadsheet tracker from Spreadsheet1.com as well.

How do I learn these techniques?

These kind of files are a great little project to build up your Excel skills. I suggest reading up below pages to learn some of the skills:

• Power Query – for connecting and getting data from anywhere (including web)
• Dynamic Array formulas – for calculating results dynamically
• Data Validation – for collecting user inputs
• Conditional Formats – for changing colors automatically
• FIFA 2018 Worldcup Tracker (another workbook to try and learn)

The post FIFA 2022 World Cup Schedule & Results – Excel [FREE Download] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Time for a quick but very handy Excel FORMULA trick.

You have two tables. ORDERS & PRODUCTS. You want to see all the orders if the product name is mentioned in the other table. Thanks to the new FILTER() function in Excel, we can do this easily now.

The data setup

For the purpose of this example, assume you have a table named orders and another table named products.

The formula to filter one table by another list

Here is the formula to fetch all orders for the products listed in the other table.

=FILTER(orders, COUNTIFS(products, orders[Product]))

How does this formula work?

This is a two-part puzzle.

PART 1 – COUNTIFS with Arrays:

Let’s look at the COUNTIFS portion first:

COUNTIFS(products, orders[Product])

This formula returns an array of 1s & 0s

• It will be 1 whenever the orders[Product] matches one of the products table items.
• Else it will be 0
• The size of this array will be same as orders table. So if orders table has 100 rows, then you will get 100 ones or zeros here.

PART 2 – FILTER

The NEW FILTER() function in Excel can take a list or table and filter it based on a criteria.

So, for example, =FILTER(orders, orders[Product]=”Milk Bars”) would filter all rows where the product names is Milk Bars.

The trick is to use COUNTIFS output (which will be a bunch of 1s & 0s) as the criteria for FILTER. When Excel sees these 1s&0s, it’s going to include the rows with 1 & exclude the rows with 0.

Thus, producing the relevant orders for us.

Here is a quick demo of the formula in action:

Problems with this approach:

While this is a great technique, if you have large amounts of data then the formula could be slow. In such cases, I recommend using Power Query to handle the job. We can use inner join in Power Query merge operation to filter one table by merging it with another table.

For more on Power Query, please read this article.

Example workbook

If you need a sample file to practice this formula, click here and download it.

How do you deal with this problem?

I have been using the FILTER(… COUNTIFS()) approach for quick scenarios and Power Query Merge option for more complicated situations. What about you? How are you solving this problem? Please share your thoughts or questions in the comments section.

The post Filter one table if the value is in another table (Formula Trick) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Let’s face it. You already know the SUMs & COUNTs of Excel. But what should you learn next? There are more than 400 functions in Excel and most of them are useless for day-to-day situations. So, in this page, let me highlight the TOP 10 EXCEL FUNCTIONS for data analysis work.

The TOP 10 Excel Functions

Here are the top 10 functions you should learn and master first. 

• SUMIFS
• XLOOKUP
• FILTER
• COUNTIFS
• INDEX

• EDATE
• UNIQUE
• TEXTJOIN
• SORT
• IFERROR

TOP 10 Excel functions - Video

If you want to understand what these functions are how to use them either read on or watch the below video. 

Play Video

1. SUMIFS

Use SUMIFS to add up values that meet one or more conditions. Example uses:

• Total sales to England
• Total donations made to charity cause A in the last 7 days

SUMIFS Syntax 

SUMIFS(values you want to add,
condition 1 range, condition 1,
condition 2 range, condition 2…)

SUMIFS example

=SUMIFS(A1:A10, B1:B10, “London”)
Adds up all values in A1:A10 where B1:B10 is London

Learn more about SUMIFS:

• SUMIFS examples & explanation
• 15 SUMIFS examples – video

2. XLOOKUP

Use XLOOKUP to search for a value in a list and return corresponding value from another list. For example:

• Get due date for invoice number 934
• Find price for the product code PR023

XLOOKUP Syntax 

XLOOKUP(value to find,
list to look in,
what do you want to get,

what to do if not found)

XLOOKUP example

=XLOOKUP(“Almond Choco”, Product[name], Product[price])
Finds the price of Almond Choco in the Product table.

Learn more about XLOOKUP:

• XLOOKUP examples & explanation
• XLOOKUP: GREAT Explanation – video

3. FILTER

Use FILTER to filter a list or table and see matching results for your criteria. This is a dynamic array function. That means, if there are more than one values, Excel automatically spills the values and shows them on the sheet. For example:

• List all sales records for “John”
• Find out which students have attended all sessions of Physics-301 class.

FILTER Syntax 

FILTER(your data, filter criteria
what to show if no values are filtered)

FILTER example

=FILTER(Product, Product[price]>20)
Lists all the Product table rows where the price is more than 20.

Learn more about FILTER:

• FILTER & Other new Dynamic Array functions
• FILTER – AWESOME explanation with practical examples – Video

4. COUNTIFS

Use COUNTIFS to count how many values meet one or more conditions in your data. For example:

• Number of times “Adam” exceeded $1,000 order value
• How many recipes use Paprika?

COUNTIFS Syntax 

COUNTIFS(condition 1 list, condition 1,
condition 2 list, condition 2…)

COUNTIFS example

=COUNTIFS(Orders[name], “Adam”, Orders[value],”>1000″)
Counts how many times Adam’s order value is more than 1000.

Learn more about COUNTIFS:

• Pattern Matching with COUNTIFS 
• 15 SUMIFS & COUNTIFS Examples – Video

5. INDEX

Use INDEX to get a value in a list by specifying the position. INDEX formula returns the reference instead of a value. This makes it quite versatile. It is my favorite Excel function.

For example:

• What is the 12th item in the invoice list?
• Get all the values in 3rd column of the order data.

INDEX Syntax 

INDEX(your data, row number, column number)

INDEX example

=INDEX(FILTER(Product[name], Product[price]>20), 3)
Returns the 3rd product name which has price more than 20. Here, INDEX is reading the output of FILTER.

Learn more about INDEX:

• 7 reasons why you should learn INDEX 
• INDEX formula – what is it and how to use it? – VIDEO

6. EDATE

Use EDATE to calculate a future or past date after a specified number of months.

For example:

• What is the date 7 months after project start date?
• What is the date exactly two years ago from today?

EDATE Syntax 

EDATE(date, months)

EDATE example

=EDATE(TODAY(), -24)

Returns the date exactly two years ago from today.

Learn more about Date functions:

• 10 tips & examples of Excel Date functions
• 3 Essential Date formulas for you – Video

7. UNIQUE

Use UNIQUE to remove any duplicates in your list. This is a Dynamic Array Function, so Excel will return and spill multiple values if needed.

For example:

• What products are sold?
• Which students have joined only one sports club?

UNIQUE Syntax 

UNIQUE(your data)

UNIQUE example

=UNIQUE(Order[Product])

Returns all the products from Order table. If a product appears multiple times, it will show up only once.

Learn more about UNIQUE function:

• UNIQUE & Other new Dynamic Array functions
• Dynamic Array functions explained – Video

8. TEXTJOIN

Use TEXTJOIN to combine a bunch of values with a specified delimiter.

For example:

• Combine all product names to one text value with comma delimited.
• All names of students who joined Maths 203 course in March 2022.

TEXTJOIN Syntax 

TEXTJOIN(delimiter, ignore empty values?, your data)

TEXTJOIN example

=TEXTJOIN(“, “,TRUE, 
FILTER(Enrollments[name], Enrollments[course]=”Maths-203″))

Combines all student names in the Maths-203 program in comma separated format.

Learn more about TEXTJOIN function:

• Excel’s TEXTJOIN function with 3 examples
• TEXTJOIN explanation – Video

9. SORT

Use SORT to sort a list or table in ascending or descending order. This is a Dynamic Array Function, so Excel will spill your outputs as needed.

For example:

• Sort the Orders table to show highest values on top.
• List students by department and name in alphabetical order.

SORT Syntax 

SORT(your data, sort column, sort order)

SORT example

=SORT(Enrollments, {1,2}, {1,1})

Sorts the Enrollments table in the ascending (alphabetical) order by department and student name (columns 1 & 2 of the table).

Learn more about SORT function:

• SORT & Other new Dynamic Array functions
• Dynamic Array functions explained – Video

10. IFERROR

Use IFERROR to stop error messages from showing up on the screen.

For example:

• Calculate sales commission but print 0 if there is an error.
• Get the 10th item of a filtered list, but show “not enough items” if there is an error.

IFERROR Syntax 

IFERROR(your formula, error message to show)

IFERROR example

=IFERROR(INDEX(FILTER(Product[name], Product[price]>20), 10), “Not enough items”)

Tries to get the 10th product with price >20, but if there is an error, prints “Not enough items”.

Learn more about IFERROR function:

• IFERROR formula – syntax, examples and ideas
• What to do when you have formula errors?

Download Example Workbook

I have prepared an Excel file with 20+ examples for these important functions. Click here to download and play with the workbook.

Change the values / formulas or use the data set to develop your own formulas.

More on Formulas & Functions

If you are just starting out, I suggest focusing and mastering the above 10 functions first. But if you are ready to next level, then go thru the below articles & resources for more.

• 35 shortcuts & tricks to make you an #AWESOME Data Analyst
• 100+ Excel formula examples
• 15+ Examples of SUMIFS & COUNTIFS (video)
• COURSE: Excel formulas & other important skills for Data Analysis – Excel School

Happy learning.

The post Which Excel Formulas should you learn first? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Recently we moved houses. And with the house move, came a brand new mortgage. But as a self-employed person with variable income, I find the steady nature of mortgage payments little hard to digest. So I wanted to know what impact it would have on my mortgage if I make arbitrary extra payments. Unfortunately, I couldn’t find such a calculator. So I made a mortgage calculator with extra payments using Excel. Here is a quick demo of the calculator. Read on if you want to know more or download this.

Mortgage Calculator - DEMO

Download the Mortgage Calculator

Click here to download the Excel Mortgage Payments Calculator

How to use this Calculator?

Refer to below illustration and click on the hot spots to understand the process. Essentially, there are 5 things you need to specify or look at when using the template.

1. Enter your loan amount
2. Enter the loan term (in years)
3. Enter the interest rate (APR)
4. Specify extra payments in the green column at relevant month rows. 
5. Look at the chart to understand the impact of your extra payments on the mortgage term.

Enter your mortgage details here.

Enter extra payment amount in relevant month rows.

The chart will show the impact!

How to create such a calculator yourself...

This part of the article discusses the process for constructing such a calculator yourself. Read on (or watch the video tutorial) if you are interested.

The key idea is…

Any extra payments you make bring down the outstanding principal of your loan, thus bringing down the “loan term”.

Step 1: Calculate the monthly (or weekly / fortnightly) payment:

Assuming you have the Loan amount, term & APR in three cells E5, E6 & E7, we can use the PMT() function to calculate the periodic payment.

In my case, let’s say loan is $500,000, term is 20 years and APR (Interest rate) is 5.35% per annum.

Then the Monthly payment would be

=PMT(E7/12,E6*12,E5)

Step 2: Set up amortization schedule

As extra payment will bring down the outstanding loan term, we need to set up an amortization table to see the impact clearly.

So, set up a range of 360 months (or longer if you want to cater for longer mortgages). You can use =SEQUENCE(360) to automatically generate all the months.

Related: Read about SEQUENCE and other Dynamic Array functions in Excel.

Your table should look like this:

1. Opening Balance is same as loan amount for month=1. For subsequent months, this will same as previous month’s closing balance.
2. Effective term is how long it would take you to pay off the mortgage based on the opening balance, and agreed upon monthly payment (calculated in Step 1) and interest rate (Cell E7). We can use NPER function to get the answer here. 
   =ROUND(NPER($E$7/12,$E$10,$D13),0) will tell us how many months it is rounded.
3. Principal Paid is the amount of principal paid in each month. We can get this with the PPMT() function. =PPMT($E$7/12,1,E13,D13)
4. Extra Payment is the input column where we can type any extra payments.
5. Closing Balance is opening balance minus principal paid minus extra payment.

Complete this table with necessary formulas and fill everything down.

Step 3: Your mortgage will end when the “Eff. Term” is 0.

Go ahead and play with the table by typing some values in the “Extra payment” column. You can see the effective term reducing. 

We can visualize the impact with a nice chart (requires some extra work) like this:

Do check the download workbook for details on how the chart is setup.

Video Tutorial - I made a mortgage calculator in Excel

Watch below video to understand how I made the mortgage calculator with flexible payments. If you prefer to see it on YouTube, click here.

More Financial Models with Excel

If you want to learn more about setting up calculators, models or business systems with Excel, check out below tutorials & examples:

Dr. Scroll-bar Mortgage Calculator or: Why you should not be borning and use form controls

Nest Egg Calculator using Power BI

Doing Cost Benefit Analysis in Excel – a case study

Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

How to embed Excel files, calculators on your website? – Step by step instructions

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

The post Mortgage Calculator with Extra Payments – Excel Download appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

SQL (Structured Query Language) is one of the most important skills for us, data people. So in this article + video, get the necessary SQL skills you need for Data Analysis work.

Step 0: Install MySQL software

I am using the FREE MySQL Community Edition software to learn & practice SQL at home. You can get it from here.

If you have any other database software available (such as SQL Server or Oracle), you can use them to follow this tutorial.

 

Step 1: Import Awesome Chocolates Dataset

You need some data to practice SQL. So I prepared a sample dataset for a fictional (but yummy) company called Awesome Chocolates.

Download the .SQL file from here.

After you have the file, 

0. Open MySQL Workbench, login if necessary
1. Click on the “server administration” tab (see illustration, click to expand)
2. Click on “Data Import/Restore”
3. Select the option “Import from self-contained file”
4. Specify the path of the downloaded awesome-chocolates-data.sql file
5. Start import

 

At the end of these steps, your MySQL should have the awesome chocolates database. Congratulations

You can see this from “Schemas” tab on the workbench

 

Step 2: Learn SQL for Data Analysis with this video

Everything is ready. Time to learn SQL.

I made an hour long tutorial to explain all the necessary SQL concepts for you. In this video, you will learn:

• How to use SELECT statement to answer business questions
• Working with WHERE clause
• Using AND, OR, NOT and combining them to create complex queries.
• Sorting query results using ORDER BY
• Combining data from two or more tables using JOINS
• Creating reports with GROUP BY
• More than 50 example queries, tips and ideas

Please watch the video below or on my YouTube Channel.

The Queries

Here are some of the example queries covered in the video lesson. Feel free to copy paste them in to SQL console to see how they work.

				
					-- Select everything from sales table

select * from sales;

-- Show just a few columns from sales table

select SaleDate, Amount, Customers from sales;
select Amount, Customers, GeoID from sales;

-- Adding a calculated column with SQL

Select SaleDate, Amount, Boxes, Amount / boxes  from sales;

-- Naming a field with AS in SQL

Select SaleDate, Amount, Boxes, Amount / boxes as 'Amount per box'  from sales;

-- Using WHERE Clause in SQL

select * from sales
where amount > 10000;

-- Showing sales data where amount is greater than 10,000 by descending order
select * from sales
where amount > 10000
order by amount desc;

-- Showing sales data where geography is g1 by product ID & 
-- descending order of amounts

select * from sales
where geoid='g1'
order by PID, Amount desc;

-- Working with dates in SQL

Select * from sales
where amount > 10000 and SaleDate >= '2022-01-01';

-- Using year() function to select all data in a specific year

select SaleDate, Amount from sales
where amount > 10000 and year(SaleDate) = 2022
order by amount desc;

-- BETWEEN condition in SQL with < & > operators

select * from sales
where boxes >0 and boxes <=50;

-- Using the between operator in SQL

select * from sales
where boxes between 0 and 50;

-- Using weekday() function in SQL

select SaleDate, Amount, Boxes, weekday(SaleDate) as 'Day of week'
from sales
where weekday(SaleDate) = 4;

-- Working with People table

select * from people;

-- OR operator in SQL

select * from people
where team = 'Delish' or team = 'Jucies';

-- IN operator in SQL

select * from people
where team in ('Delish','Jucies');

-- LIKE operator in SQL

select * from people
where salesperson like 'B%';

select * from people
where salesperson like '%B%';

select * from sales;

-- Using CASE to create branching logic in SQL

select 	SaleDate, Amount, 
		case 	when amount < 1000 then 'Under 1k'
				when amount < 5000 then 'Under 5k'
                when amount < 10000 then 'Under 10k'
			else '10k or more'
		end as 'Amount category'
from sales;

-- GROUP BY in SQL

select team, count(*) from people
group by team
				
			

SQL Practice Problems

Once you understand the concepts I’ve demoed in the video, try to solve below homework problems.

If you want to cheat, use the solutions tab to see the answers.

Homework Problems

Solutions (cheating)

Homework Problems

Solutions (cheating)

Resources to Learn More

SQL is a great skill to have if you work with data. Please use below courses, books, articles & websites to learn more.

SQL BOOKs

I recommend getting these SQL books.

• SQL Quick Start Guide by Walter Shields
• SQL for Data Analysis by Cathy T
• SQL All-in-One for Dummies by Allen G. Taylor

SQL COURSEs

I recommend trying out these courses on SkillShare academy.

• SQL 101 by Alvin Wan
• SQL Database & Queries

SQL WEBSITEs

Do check out these helpful websites to learn and understand various SQL concepts.

• W3Schools SQL
• Introduction to SQL by Khan Academy
• Introduction to T-SQL by Microsoft Docs

If you use my links to purchase the books or courses, I get a small affiliate commission.

There is no extra cost to you, obviously.

SQL Alternatives

If you want an alternative to SQL, consider learning Power Query.

Here is an article and here is a video to help you with that.

 

All the best

I wish you all the best with your SQL learning. Do let me know in the comments below if you have enjoyed this article and the video.

The post Learn SQL for Data Analysis in one hour appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

One of the easiest ways to impress your audience with Power BI is a tooltip. This simple idea can provide incredible value to the report readers and elevate your work to next level. In this easy tutorial, learn how to set up and work with tooltips in Power BI.

Power BI Tooltip

A quick demo

Step 1: Create the main visual

Create a visual on the page where you want your tooltip. Let’s say you want to show the headcount by department in a column chart, like below:

? Tip: New to Power BI? Try this getting started tutorial first.

Step 2: Add a new page and make it “tooltip” page

Insert a new Power BI report page. Click anywhere on the page and go to “format page” area, in the format pane.

Setting up a tooltip page

1. Click on the “format” options
2. Go to “Canvas settings”
3. Set the page type as “tooltip” from “16:9”

Step 3: Build your tooltip page

On this “new” page, just add whatever visuals, images & textboxes you want to show. For example, I want to show gender & employee type distribution. So I added those visuals.

? Tip: Make sure you give your “tooltip” page a name.

Step 4: Link tooltip page to main visual

Go back to the report page. Select your main visual. Then link it to the tooltip page using below steps.

1. Go to “format” and “general”
2. Select the “tooltip” area.
3. Set the type to “Report page”
4. Select the page from the list of pages.

Voila! Your tooltip is now set up.

Bonus tips:

• You can put anything on the tooltip page and it will show up. For example, here is a fancy tooltip. It has page background color, DAX measure (with SELECTEDVALUE) to show the department name in the heading.

• Don’t show too much detail or tables in tooltips. As tooltips are ephemeral it is a good idea to keep them simple.
• You can customize the tooltip size! When creating the tooltip page (step 2), you can use “Custom” as page size and set up a different size or shaped tooltip.
• Use tooltips to show helpful hints or messages. You can get creative with tooltips by linking them to a card or table visual to show help messages, explanations or other useful text. See this demo:

Try tooltips (here is a sample file)

So what are you waiting for? Give them a try.

If you need a hand with the tooltips, here is my sample Power BI workbook.

Learn more about Power BI

Want to learn Power BI? Check out below articles + videos.

• Introduction to Power BI and your first project (article + files)
• Getting started with Power BI (video + files)
• How to use Power Query (article, 4 practical examples + files)
• Full course on Power BI

The post How to create interactive tooltips in Power BI (step by step) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Python is a great programming language to learn for anyone in data profession. For people with prior Excel skills and a little bit of programming bent, Python is a valuable skill to acquire. Using Python, we can:

• Automate data collection, clean-up and manipulation
• Create machine learning models, complex data analysis systems
• Produce interesting and advanced data visualizations
• Make interactive windows applications & websites 
• Or just have fun learning and solving problems

Python & me

One of my learning goals for 2022 has been to learn Python. I have been learning how to write Python code and use Python to solve real-world problems since January 2022. In fact, all of us: Jo(wife), Nishanth(son) & Nakshatra(daughter) have been learning Python every week for the last few months. 

So in this article, let me share some resources on how to learn Python (for an Excel person).

Learning Python: Getting started

Download Python:

The first step is to download Python & set up a coding environment.

• Download latest version of Python
• Follow the instructions on the download page to install Python on your computer.

Set-up a coding environment:

Once Python is installed, you may want to also set-up a coding environment. This allows you to write code, execute, debug &  manage files with ease.

• I have been using the excellent Visual Studio Code by Microsoft.
• You can download and install Visual Studio Code (VS Code for short) from here. 

Your first program

Writing a hello world program is the sacred ritual in software development. To create a simple python program that prints the message hello world use below code:

				
					print("hello world")
				
			

Hello World v2.0

You can easily enhance that code to ask the user for their name first and then print a hello message.

For that, you can use the input() function in Python, like this:

				
					name = input("What is your name? ")

print("Hello " + name+ ", This is my first Python program")
				
			

Going beyond: 1+ hour Python Tutorial

I just posted a 1+ hour video on how to write Python code with many examples & 3 projects on my YouTube channel. So if you want to learn a bit more about Python coding, please watch it below or on my channel.

The video covers below topics in detail.

• Getting started with Python
• Working with variables in Python
• IF condition, indentation in Python
• While loop in Python
• Building a simple expense calculator with Python
• Working with Lists in Python
• Using For loop when navigating a list
• Generating list of values with loops
• List comprehension in Python
• How to access an item or a part of list in Python
• Creating a multiplication table with Python lists
• Building an enhanced expense calculator with Python
• Working with CSV & Datetime libraries in Python
• How to execute Python code from command line
• How to learn more – My top 6 tips

Sample Files

As part of the above video, I build many Python examples & 3 mini-projects. 

• Click here to download the source code files (zipped)

The code samples:

• hello.py: Hello world
• agecheck.py: Age check program
• expensecalc.py: Simple expense calculator
• lists.py: Working with lists
• multiplication-table.py: Working with lists & list comprehensions
• expensecalc2.py: Improved expenses calculator
• trackerproject.py: Expense tracker app with Python

Learn more - Python Resources

As I said earlier, Python is a fun and useful language to learn. If you like a bit of challenge, I highly recommend learning Python. Here are a few of my favorite resources to learn & use Python.

Software tools:

• Visual Studio Code for building and managing Python files

Python Books:

• Python Crash Course 2nd Edition by Eric Matthes: This is the book we all (Jo, kids & I) read and really loved it. The explanations and examples are easy enough to get started. There is enough variety to please everyone. Pictured aside is my son with the book
• Automate boring stuff with Python: More practical if you want to get things done with Python. I read it a few times and really like the practicality of the book. 
• Python Data Science Handbook: Python is particularly useful for doing data science & building machine learning models. This is an area of focus for me in the next months. I suggest getting the Python Data Science book once you have strong foundation in the language.

Python related Channels:

Youtube is another popular way to learn Python coding. Here is another excellent getting started tutorial:

• Learn Python – 1hour course by Mosh

Python Courses:

I recommend Coding 101: Python for Beginners by Alvin Wan on SkillShare. It is a concise and practical course that should give you enough confidence to build more powerful code. Check out the class here (you get 1 month free access to SkillShare when you join with my link).

 

Wish you all the best

Note: If you purchase the course or books using my links, I receive a small commission. My recommendation is based purely on the merit of them rather than the payout.

Do you code in Python? Tell me more...

Using the comments below, Do let me know what you use Python for and how you make it part of your work / personal life projects. 

 

The post How to learn Python as an “Excel person”? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

One of my all time favorite comedies is The Hangover. It is the story of four friends – Alan, Stu, Doug & Phil going to Las Vegas for an (un)forgettable night of adventure and mayhem. While the movie did end on a happy note, they forgot one little thing.

They didn’t share the expenses!

So in this installment of Excel Homework, can you split the expenses?

The Problem

Assuming you have below data (feel free to copy paste it in to your Excel, or use this workbook):

				
					Expense Details
Phil: 255.12,121.29,160.23
Alan: 43.17,226.76,343.66,358.01
Phil: 159.95,177.56,278.37
Stu: 206.51,188.09,244.68,33.21,152.98
Phil: 285.91,306.4,140.68
Doug: 226.76,226.76,169.6,208.67
Phil: 326.4
Phil: 308.43,224.95,102.33
Phil: 168.64
Stu: 259.93
Stu: 121.29
Stu: 177
Alan: 172.23
Alan: 214.04,162.58,205.66,188.16,128.41
Alan: 62.75,160.51,173.34,283.8
Phil: 356.3,361.34,41.35,255.94,67.42
Stu: 168.25,161.28,248.45
Doug: 163.93,308.43,218.58
Phil: 226.04,265.97,140.81,197.45
Doug: 216.79,180.39,246.65,149.55

				
			

Your need to generate a a table like this to show who needs to pay whom & how much.

Get cracking then!

Sample file & Your Solution:

Click here to download the sample file with the expenses data. 

You can use any Excel feature to come up with the answer. 

Tell me how you solved it using the comments box below

Watch my solution:

I made a video about this problem and one approach to solve it (using Power Query and Dynamic Array Formulas). Check it out below or on my Channel.

More homework problems:

Need a challenge? I got one or many for you:

• Write a formula for department monthly budget
• Can you find the secret code in the data?
• Can you match the job titles?
• More homework problems

The post Can you split “The Hangover” expenses? [Excel Homework] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Ladies & gentlemen… Excel has a new function. LAMBDA()  is the new addition to Excel’s family of functions. Using LAMBDA, we can create custom & reusable functions in Excel. 

In this article, learn all about:

• What is LAMBDA()
• How to create LAMBDAs in Excel?
• How to re-use LAMBDAs
• 4 Practical Examples of LAMBDA
• What are LAMBDA helper functions?
• Sharing LAMBDAs with others
• How LAMBDA compares with other custom function options in Excel?

LAMBDA - Who can use it?

LAMBDA() and other lambda helper functions are available in below formats of Excel only.

• Excel 365
• Excel on the Web
• iOS & Android Excel

If you are using an older version of Excel (Excel 2019 / 2016…) then you can test and practice LAMBDA from Excel on the web. Just go to https://excel.new

What is LAMBDA & How to create it?

LAMBDA (pronounced lam-duh) allows us to write our own functions and add them to the library. 

For example, here is a very simple LAMBDA to subtract one value from another.

				
					=LAMBDA(a, b, a-b)
				
			

To call this LAMBDA, you can use the below syntax.

				
					=LAMBDA(a, b, a-b)(90, 12)

' The result will be 78
				
			

Here is a quick demo of our subtraction LAMBDA…

How to use LAMBDA in Excel - Video

I made a video about LAMBDAs. Please watch the video for a bit more explanation and demos. (Watch on Youtube).

How to "install" & "reuse" LAMBDA?

The TRUE Power of LAMBDA is unleashed once you “install” or “setup” them. To install out subtract lambda, 

1. Go to Formulas ribbon and click on “Define Name”.
2. Type a name for the lambda, such as subtract
3. In the “Refers to:” area of the name, type your lambda. For ex:
   1. =LAMBDA(a, b, a-b)
4. Click OK. 
5. That is all. Our SUBTRACT LAMBDA is setup.

To call the lambda, just go to any cell and type the =subtract formula and provide parameters a & b as you normally would.

How awesome is that.

Here is a quick demo of the LAMBDA setup process.

Practical Examples of LAMBDA

Let’s be honest. No one needs the SUBTRACT function. We can already do that with the – (minus) operator. So in this part of the article, let me present some practical LAMBDAs. 

 

Weighted Averages

Calculating weighted averages is a pain in Excel. There is no built-in function to do the task. Well, we can make our own WAVERAGE() function, thanks to LAMBDA().

Define WAVERAGE name as

=LAMBDA(values, weights, SUMPRODUCT(values, weights)/ SUM(weights))

Here is how you can use it to correctly calculate weighted average of salaries.

Top N

Let’s say you have a list of values and just want to get the “topn” values in descending order. We can set up a LAMBDA to do that magic.

Define TOPN name as

=LAMBDA(values, n, LARGE(values, SEQUENCE(n)))

We can call this LAMBDA like:

=TOPN(a1:a10, 3) to see top 3 values in the range A1:A10.

Tomorrow

Excel has the excellent TODAY() function to tell me what is the current date. But there is no TOMORROW()?

Let’s fix that with LAMBDA.

Define the name tomorrow as,

=LAMBDA(TODAY()+1)

And, you can call it by writing =TOMORROW() in any cell. Works like magic…

First Monday of the Month

You can write complex Date logic to figure that out. But who wants to write the same formulas every time. This is the job for LAMBDA.

Define FirstMonday as,

=LAMBDA(anydate,
LET(som, DATE(YEAR(anydate),MONTH(anydate),1), CHOOSE(WEEKDAY(som),1,0,6,5,4,3,2)+som))

And to get the first Monday of any month, you can use =FIRSTMONDAY(any date)

For example, =FIRSTMONDAY(“1-jan-2022”) will be 3-Jan-2022 (which is the first monday).

What are LAMBDA "helper" functions...?

Apart from LAMBDA, Microsoft also introduced a bunch of helper functions so we can truly build any kind of LAMBDAs in Excel.

These are,

• MAP – to map (or apply) a LAMBDA to a range of values and return a range of outputs.
• REDUCE – to take a bunch of values (or lambda outputs) and reduce them to a single value
• SCAN – Returns intermediate results of applying a lambda on an array – one step at a time.
• MAKEARRAY – gives you an array of specific size (rows by columns) by applying the lambdas
• BYCOL – applies a lambda function to a range, one column at a time and returns a row of outputs.
• BYROW – revers of BYCOL…

Read more about the LAMBDA Helper functions here.

How to "share" LAMBDAs with others?

By default, any LAMBDA you create is only available in the workbook. To share LAMBDAs across files or with other users, you can try:

• Simpler option: Email or share the files with others. When they open the file, they can see & use the LAMBDAs in that file or copy paste the names to their own files.
• Better option: You can use the newly added “Advanced Formula Editor” to share and centrally publish lambdas (with Github). This is for advanced users only. You can get the add-on here.

Download - Excel LAMBDA Examples

Click here to download sample workbook with 4 LAMBDAs. Play with the functions or make your own.

How does LAMBDA compare with UDFs?

Seasoned users of Excel know that we already have the ability to create and share our own functions. It is called user defined functions (or UDFs). 

Here are some points on how UDFs compare with LAMBDA.

• UDFs rely on VBA (or add-ins). If you disable the add-in or don’t load the VBA code, then the UDF won’t work.
• LAMBDAs are part of core Excel, so they will run without any permission or loading.
• UDFs are easy to share, but LAMBDAs have no such option yet.
• UDFs work in all desktop versions of Excel. LAMBDA is on available in Excel 365 but they also work on Excel on Web & on apps.
• UDFs let you access Visual Basic object model (restricted) and do things on your spreadsheet that are not possible with formulas (for ex: extract comment in a cell or find the color of a cell). 

 

Learn more about LAMBDA in Excel...

For more information about LAMBDA, please read below pages too:

• Excel LAMBDA help page from Microsoft
• How to use Excel LAMBDA by ExcelJet
• What makes LAMBDA so awesome by Felix @ XLWings
• Advanced Formula Environment add-in from Microsoft (for creating and editing LAMBDAs)

The post What is LAMBDA? 4 Practical examples to REALLY understand it appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Do you want a stylish, interactive and useful monthly calendar & planner template? Then please use below link to download it. A new year gift to you from Chandoo.org ?

Features of the Calendar:

• Works for any month of the year 2022
• Big cells with enough space to jot down notes when printed
• Works in any version of Excel

Features of the Planner:

• A customizable data entry worksheet for activities + events by date
• A set of icons to tag activities
• Works for any month
• Needs Excel 365

Want to know how this is created?

I made a video explaining the calendar + planner logic, formulas and construction. Please watch it here.

Need more templates?

• Annual calendar (works for any year)
• Todo list template
• Project Plan template
• Project management templates (paid)

The post FREE 2022 Calendar & Monthly Planner Printable Template appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Hello readers & fans,

On behalf of my family, our staff, volunteers & well-wishers, Let me wish you a very happy Christmas & prosperous new year 2022. Here is a small holiday card from Chandoo.org HQ.

About this year’s card: (From left: Nishanth, Jo, Nakshu & Chandoo) We went to the beautiful Queenstown this October to celebrate my 40th (XL Birthday!!!) and took this picture just before a sunset. The kids are now taller than mum and as tall as me. We will be taking family pictures sitting down from now on, so that they don’t look like giants. ?

Thank you

2021 has been a very positive and inspiring year for me. I found a renewed sense of mission to learn, grow & share with the world. Even though I did not blog much this year, I have been very active on YouTube, building a community of Excel / Power BI learners. I want to take a minute to acknowledge some of the people & organizations that helped me this year.

• You – for reading my articles, watching the videos or learning from a course. Thank you for making me your learning partner.
• My family – for supporting & loving me all this while. My kids & Jo also played an important role in filming or giving feedback on my videos.
• Microsoft – for creating Excel & Power BI.
• YouTube – for providing an awesome platform to learn & share
• My teachers – too many to name, but thanks for guiding and inspiring me. Some quick shout outs
  • YouTube & Video related: Dusty Porter, Tim & team at Video Creators
  • Life & Inspiration: Wheezy Waiter, Ali Abdaal, Simone Giertz, ElectroBoom

• Conference & events: This year I had the fortune of presenting virtually at many meetups and events. Special thanks to Vena Corp & Global Excel Summit.
• My staff – Ravindra & Pothi for keeping the gears turning and helping me focus.
• My customers – thanks to more than 1,200 people who bought a course, template, e-book or live cohort membership this year and supported me.
• My Supporters – Thanks for more than 145 people who donated a superchat / supersticker during my YouTube live streams this year.
• Our forum regulars & mods – thanks for taking time to help and share your knowledge.
• Blog & channel friends – for recommending & sending people my way. Big thanks to Bill Jelen, Danielle, Ken Puls, Alan Murray, Sumit Bansal & Boriana for sharing a few tips on the channel
• Software providers – for helping me run the site & channel. Big thanks to WordPress, Elementor, Stripe, E-junkie, PayPal, Wishlistmember, KnownHost, ConvertKit, Amazon, Gmail, Techsmith, EpidemicSound, Unsplash, Pexels & Pixabay

This holidays…

We (Jo & I) are taking some time off the work and traveling with kids to South Island on a road trip. We will be visiting Christchurch & Kaikoura for 10 days. I will be sipping beer on the beaches or going for a swim between now & 4th of Jan.

Once again, I wish you a very happy Christmas & a prosperous New Year 2022.

The post Merry Christmas & Happy New Year 2022 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

				
					=XLOOKUP(MAX(D6:D45),D6:D45,C6:C45)
				
			

				
					=XLOOKUP(LARGE(D6:D45, 2),D6:D45,C6:C45)
				
			

The post How to write complex Excel formulas (hint: it’s a lot like LEGO) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.