Today, let’s learn how to make an impressive map chart with Excel. Something like this:

Step 1: Set up your Data for the Map Chart

You need geographical fields (district / county/ state or country), few numeric values to create the map chart in Excel. Here is the layout of the data I’ve used.

I will explain the logic for “Calc. Column” in Step 3.

Step 2: Create “selection mechanism” with Data Validation

Select a blank cell to set up “drop-down” selection mechanism. This is where we will select one of the products to show on the map.

Go to Data ribbon & click on “Data Validation”. Change the rule type to “List” and point to the list of products. You can set up this list elsewhere in the spreadsheet or directly point to the table headers.

Related: Learn more about setting up “drop-down lists in Excel”

Step 3: Calculate the values for “selected” product

I recommend using either XLOOKUP or INDEX MATCH to calculate the values of “selected” product for our table.

Here is the XLOOKUP formula I’ve used. This assumes the drop-down list selection is in cell G6.

=XLOOKUP($G$6,
Table2[[#Headers],[Mint Chip Choco]:[After Nines]],
Table2[@[Mint Chip Choco]:[After Nines]])

The above formula looks up the G6 (selection values) against table headers and returns the corresponding product sales values to the calculated column.

Step 4: Make the Map chart

Select the geographical fields & the calculated columns alone, then go to Insert Ribbon & click on the Filled Map chart option (see below).

Customize the map chart colors & title if you want.

Place chart next to the selection cell and you will have a dynamic map chart in Excel, like below:

Download the sample file

If you need a hand with this technique, check out the example workbook.

Video Tutorial – How to make map charts in Excel?

I made a video explaining this process with a few more examples & tips. Check out the video below or my YouTube channel.

Known issues & problems with Maps in Excel

Excel map charts are not perfect. Here are some known issues with it.

• Not all geographical data is supported: In many countries, you can’t show data by district or city or regions. For example, if you try to make the same chart with districts of an Indian state, the map will not work.
• No support for LAT / LONG or street addresses: You can’t create map charts with specific point information (like addresses or coordinates).
• Incorrect maps: When a geographical boundary is disputed (due to wars or whatever), the map chart doesn’t show correct outlines or altogether omits the regions.
• Unable to zoom: You can’t zoom in to a specific region with the map charts.

Possible Alternatives:

You can use Power BI or Tableau other data visualization tools to make the maps. They allow working with more granular data or can let you control the zoom for your maps.

Got a question?

Do you have a question or doubt about using map charts in Excel? Leave a comment so I can help.

The post Make an Impressive Interactive Map Chart in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Microsoft Excel is a great tool for creating business dashboards. Recently, I made the below dashboard using Excel in just 5 steps.

Step 1: Figure out what you need

The first step for any successful dashboard is to understand what information your users want and how they want it.

• Talk to your users and ask them “what and why” they need the information.
• Prepare a mock-up sketch of the dashboard and get the buy-in.
• Identify data sources and refresh options.

At the end of STEP 1, you should have a clear idea about where you data is, how it should be gathered, analyzed and presented.

Step 2: Clean & Set up data

If you look long and hard, you can find the Loch Ness Monster, the truth about Bermuda Triangle before you locate the Perfectly Clean Business Data.

Chandoo Tweet

The next step is to clean & structure your data in a tabular format.

• Use Power Query to connect to the data source(s) and fetch the data.
• Set up necessary data cleaning processes with in Power Query.
• Load the data to Excel either as tables or to the data model.

At the end of this step, you should have one or more tables of data in Excel.

Step 3: Calculate the KPIs with Pivots

Load the cleaned data into “data model” based pivot tables in Excel. Now, using Power Pivot DAX measures, calculate the necessary KPIs.

For example, for the above business dashboard, I’ve calculated:

• Total Sales =SUM(sales[Sales])
• Total Boxes = SUM(sales[Boxes])
• Total Expenses = SUM(sales[Expenses])
• Total Profit = [Total Sales] – [Total Profit]
• Profit % = [Total Profit] / [Total Sales]

Create the pivot tables using these measures.

Related: Learn more about Power Pivot & DAX in Excel.

Step 4: Make Interactive Charts

First create the necessary pivot charts (refer to the mock-up you made in step 1). 

Then, make them interactive using Slicers, Timelines or Report Filters.

Learn how to create a slicer here.

Step 5: Bring Everything Together & Format

We are ready for the big finale!

Bring all the charts, KPIs and slicers you have created so far to a new sheet. (Cut & Paste).

Arrange them neatly and format as needed.

Viola, your dashboard is ready!

Video Tutorial - How to create a dynamic business dashboard

I’ve created a 30 minute tutorial on how to create the above dashboard with all the necessary steps. Watch it below or on my Youtube channel.

Sample Workbooks

Please download the blank template file from here.

If you want the full dashboard (with al the workings), you can find it in my Excel School Online Course.

More Dashboard Tutorials & Examples

I have a confession to make. Dashboards are kind of my thing. So I have been writing about them for almost 15 years now. Here are some of my best Excel & Power BI dashboards.

Designing a dashboard to track Employee vacations [case study]

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

D’oh – Visualizing Homer’s favorite sayings in Power BI

How to create a fully interactive Project Dashboard with Excel – Tutorial

Easy Website Metrics Dashboard with Excel

Employee Turnover Dashboard – Power BI for HR

The post How to Create a Dynamic Excel Dashboard in Just 5 Steps appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Excel formulas acting slow? Today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.

10 Tips to Optimize & Speed up Excel Formulas

1. Use tables to hold the data

Starting Excel 2007 you can keep all the related data in a table. For example call center data in our recent dashboard is kept in a table. Tables can be used in formulas with structural references, can be used as a source for pivot tables etc. And since tables grow & shrink as you add / remove data, none of your formulas need to be dynamic. As an example, if you have table called cs, then the formula sum(cs[column_name]) refers to sum of all values in the column_name of table cs. Even if you add more data to CS, the formula still works.

Resources to learn about Excel Tables:

• Introduction to Excel Tables – what are they and how to use them?
• Example: Customer Service Dashboard – Data & Calculations

2. Use named ranges, named formulas

By using names and named formulas, you can simplify your spreadsheet. Not only that, since named ranges & named formulas can hold arrays (ie lists of values), you can hold intermediate results or values that you need to refer many times in these named formulas. This will reduce the formula overhead and makes your workbooks faster.

Resources to learn about named ranges & named formulas:

• Excel School Program: In this comprehensive course, I talk about how to think about and write better formulas for data analysis work.
• Musings on Live Calendar [Excel Hero]
• Examples of Named Formulas – 2023 Calendar in Excel

3. Use Dynamic Arrays & Spill Ranges

Introduced in Excel 365, Dynamic Arrays allow us to build complex calculations with ease. I suggest incorporating new functions like:

• FILTER to fetch a list of values that meet one or more criteria.
• SORT to sort the values
• UNIQUE to eliminate duplicate values on the fly
• XLOOKUP to perform various lookups
• VSTACK / HSTACK to combine datasets
• TOCOL / TOROW to convert tables of data to single row or column formats
• # or Spill operator to manage spill ranges

Learn more about Dynamic Array functions here:

• Dynamic Array Functions – A deep introduction
• Dynamic Array Functions – how to use them [Video]
• How to use XLOOKUP

4. Use Pivot Tables

Many times, even when we do not need formulas we use them, because we can. Pivot tables are an excellent way to calculate a lot of summary values with few clicks. Once the pivot is built, you can refer to the pivot values with GETPIVOTDATA or simple cell references. This will reduce a lot of unnecessary calculations. If you are changing the data, you can just go to DATA ribbon and refresh all pivots in one go. This process works smoothly when you use tables to hold the data.

One of the reasons for slow workbooks is lot of data. Since, pivot tables are designed to work with lots of data, by using them, you can speed up your workbooks.

Resources to learn Pivot Tables:

• Introduction to Excel Pivot Tables
• Examples, Details & Downloads on Excel Pivot Tables

5. Sort your data

One of the reasons for sluggish performance is that you are searching for something in a lot of un-sorted data. You are making Excel look for a needle in a hay-stack. Many times we inherit un-sorted data thru data imports. By sorting the data & using correct operators in lookup formulas, we can instantly speedup a sluggish workbook. If you feel that sorting the data is a pain, you can even automate it with Power Query or a sort procedure (thru a simple VBA macro).

Examples on Sorting:

• Remove duplicates & sort a list using Pivot Tables
• Use Power Query to pre-sort the data you are working with

6. Use Manual Calculation Mode

Speed is the hefty price you pay for complexity. But many times, we want our Excel workbooks to be complex, because only then they would reflect real world. In such cases, you can set formula calculations to manual mode.

Just press F9 whenever you want to run the formulas. Please note that Excel runs formulas whenever you save the file too.

7. Use Non-volatile formulas

There are a class of formulas in excel called as volatile formulas. These formulas are re-calculated whenever there is a change in the workbook. Examples of volatile formulas are RAND, NOW, TODAY, OFFSET etc. So when your worksheet has a lot of volatile formulas, any time you make a change all these formulas must be re-calculated. Thus, your worksheet becomes slow.

Solution? Simple, do not use volatile formulas. For example, instead of using OFFSET to construct a dynamic range, you can use INDEX. Since INDEX is non-volatile, it tends to be faster. Or better still, use a table.

Resources to learn more:

• Excel INDEX function
• Volatile functions in Excel

8. Keep formulas in a separate sheet

Formulas are the driving force behind any Excel workbook or model. By keeping all them in a separate worksheet(s), you minimize the chance of mistakes, omissions or repetitions.  Debugging or investigating slow performance becomes an easy task when all formulas are in same place. I usually keep all the formulas in one sheet whenever I am designing a dashboard or complex workbook. This structure also helps me in thinking thru various calculations and planning the formulas in a structured way.

9. Write better formulas

Here are some guidelines that I follow when writing formulas.

• Built-in formulas tend to better than your own version – for example SUMIFS is easier to write and just as fast as SUMPRODUCT.
• Do not refer to entire column when you need just a few values. Do not write SUM(A:A), when you know values are only in A1:A10.
• IFERROR instead of lengthy IF(ISERROR formulas. Use IFERROR to simplify your error checking.
• Remove or Fix formula errors [how to, findout why formulas are not working]
• Use newer Dynamic Array formulas instead of old clunky array formulas
• Remove or Reduce references to other workbooks. Use Power Query instead.
• Remove any named ranges that result in error or missing links.
• Try to come up with alternative formulas: this not only sharpens your mind, but lets you discover better solutions.
• Do not calculate something if you do not need it.
• Do not calculate same thing twice. Use the first result second time too. Use LET for accomplishing this.

Resources to write better formulas:

• Introduction to SUMIFS formula
• Introduction to XLOOKUP formula
• How to use the new Dynamic Array functions in Excel
• Introduction to SUMPRODUCT formula
• Introduction to IFERROR formula
• Excel Formula Forensics
• Excel School program

10. Desperate times need desperate measures

Sometimes, no matter what you do, the workbook remains slow. Here are a few whacky ideas that I try in such cases:

• Replace formulas with values. I take a backup of the formulas. Then I select everything, CTRL+C, ALT+ESV (or CTRL Shift V). Done!
• Develop the workbook from scratch: Sometimes it helps to design the workbook afresh.
• Replace external data links with actual data: And import data by copy-pasting if needed.
• Reduce the functionality: See if the end user can live with fewer features in the workbook.
• Find an alternative solution: Trying to do everything in Excel is foolish. See if there is any external tool that can do this better & faster.

BONUS: Learn new formulas & play with them

Optimization is not a one-shot exercise. It is an ongoing-business. So you need to constantly learn new formulas, new uses & play with them. This way, you see new ways to improve a sluggish workbook. To begin with, explore our Formula homework & formula forensics pages and see how you solve these problems.

How do you speed-up your Excel formulas?

So how do you optimize & speed-up your Excel formulas? What techniques do you use? Please share using comments.

The post Speed up your Excel Formulas [10 Practical Tips] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Recently I optimized a pretty long nested IF formula using a simple but elegant trick. I made it 80% shorter! In this article, let me explain the process and share the formulas.

The Situation: Discount Calculation

Imagine you have to calculate the appropriate discount using below rules.

And you have transaction data like below:

How would we write the formula in column H?

Option 1: Long, Nested IFs

We can calculate the discount by writing a complex, lengthy and nested IF formula.

Here is one such formula:

				
					=LET(cat, XLOOKUP(F5,products[Name], products[Category]),
        IFS(OR(D5="India", D5="USA",D5="UK"),
            IFS(AND(G5>=200, G5<=999), 
                IF(OR(cat="Bars", cat="bites"),
                        IF(E5="New",Rules!$F$7, Rules!$F$8),
                    0),
                AND(G5>=1000, G5<=1999), 
                    IFS(cat="Bars", 
                            IF(E5="New",Rules!$F$11, Rules!$F$12), 
                        cat="Bites", Rules!$F$14,TRUE, 0),
                G5>=2000, 
                    IF(E5="New", Rules!$F$16,
                        IFS(
                            cat="Bars",Rules!$F$17, 
                            cat="Bites", Rules!$F$18, 
                            TRUE, 0)
                    )
                )
            )
        )
				
			

What this formula is doing?

1. We start by calculating the “category” of the product using XLOOKUP and storing it in the variable cat
2. Then we check the rules (refer to picture 1 above)
3. Once we reach the lowest level of the rule, we get the matching discount from the rules worksheet cells.
4. If no discount applies, we return 0

What is wrong with this formula?

Long and error prone

Such formulas are too long to write correctly and errors are not easy to catch.

Not easy to update

When the business rules change, the formulas become hard to edit.

Hard to maintain

The formulas become a nightmare to maintain and document.

Option 2: A smart alternative

What if we can rewrite the rules so that we can write shorter formulas?

I suggest rewriting such business rules as a table like this:

Once you have such a table, we can then rewrite our formula using SUMIFS & wildcard characters. Like below:

				
					=LET(cat, "*"&XLOOKUP(F5,products[Name],products[Category])&"*",
        SUMIFS( discount[Discount],
                discount[Country],"*"&D5&"*",
                discount[Category],cat, discount[Customer Type],"*"&E5&"*",
                discount[Quantity from],"<="&G5,
                discount[Quantity to],">="&G5))
				
			

How does this formula work?

1. We start by calculating the “category” of the product using XLOOKUP, pad this with * and store it in the variable cat
2. Then we use SUMIFS to add up [Discount] column by 
   1. Checking the country (in D5) against [Country] column of discount table
   2. cat against [Category] column
   3. Customer type (E5) with [customer type] column
   4. Quantity (G5) with the to & from ranges
3. If there are no discounts then the SUMIFS would be 0
4. Else it would tell us what the discount is.

Advantages of the SUMIFS approach

Easy to write & test

The formula is easier to write and test. Hence fewer errors.

Easy to maintain & update

Whenever the business rules change or new products are added, updating the discount table is all you need to do.

Scalable

Even when you have 100s of rules, this table approach is easy to scale and won't increase the formula size.

Use FILTER to work with text

If you need to get a non-numeric value as the output, we can use FILTER() instead of SUMIFS.

Video Tutorial - Honey, I shrunk the formulas

I made a video about this concept. See it below or click here to watch it on my YouTube channel.

Try it yourself - Here is a sample file

Want to have a play with the data and see which formula is easier to write?

Click here to download the sample file.

Your thoughts on this approach?

What do you think about this approach? Leave a comment. 

Also, do check out below pages for more on other ways to make advanced Excel formulas.

• Introduction to SUMIFS
• Introduction + examples on XLOOKUP
• Use LAMBDA to create new functions
• New Dynamic Array functions in Excel
• Excel Formula Forensics

The post A clever technique to simplify your long, nested IF formulas appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

In this hands-on tutorial learn how to create a simple, elegant and functional to-do list to track your projects and tasks.

Download FREE To-Do List Excel Template

Don’t want to make your formulas and formatting? You can download my ready-to-use To-Do List file and change the data as per your project.

Click here to download the template.

Step 1: Set up your To-do data tables

We need two tables of data to begin with.

Table1: this is our to-do activity data table. It should have activity, optional due date and status columns at the minimum.

 

Table 2: This table lists all the possible status options. You can load them with below values (or come up with your own statuses).

Step 2: Link Status options to the todo Table

Once we link the “status” and “todo” tables, we can easily update the todo status, as demoed below:

To do this (excuse the pun),

1. Select the “todo status” column in our second table
2. Use “Formula” ribbon > Define Name button (ALT M M), and create a name for this column. I named mine status_options
3. Go back to the “Todo” table and select the status column.
4. Go to Data ribbon > Data Validation and set up a List validation with source as status_options

Now our status column is linked with the options we have defined.

Step 3: Create the CANVAS for visual To-Do List

Let’s setup the canvas for our todo list. Add a blank sheet and,

1. Make three columns wide enough to show the todo activities
2. Add a title and format it nicely. I used the “shapes” in insert ribbon for this.
3. Add a footer and do the same.
4. Add background colors and tidy up as needed.

Here is a high-speed show-reel of my formatting setup.

Step 4: Formula Time

Time for some number crunching. We need to use two formulas to get the ongoing activity list and structure it for our output page.

Add another sheet for the calculations and set up below two formulas.

The formulas

Our first formula (set up in cell B3 in the calculation worksheet) fetches all the ongoing activities and sorts the data using FILTER & SORT functions.

				
					=SORT(FILTER(todo[[Activity]:[Due-date?]],(todo[Status]="Ongoing")+(todo[Status]="")),2,1)
				
			

The second formula rearranges this data in to a single column using the TOCOL() function.

				
					=TOCOLO(B3#)
				
			

LAST STEP: Bring everything together to make the To-Do List

Go the canvas todo list we created in step 3 and get first 12 rows of our TOCOL() output for first column, next 12 rows for the middle column and the next 12 rows for the last column.

Tidy up and format as needed.

Tadaaaa, our To-Do List is ready!

Video Tutorial - How to make a To-Do List in Excel?

If you need more help with these instructions, check out the video tutorial I made below or on my YouTube Channel.

Download FREE To-Do List Excel Template

Don’t want to make your formulas and formatting? You can download my ready-to-use To-Do List file and change the data as per your project.

Click here to download the template.

Ready to use Project Management Templates

Create Gantt charts, Project Dashboards, Burn Down Charts and Risk Trackers with your data in just minutes.

Get my ready to use Excel Project Management Bundle from here.

The post Create a beautiful, elegant & interactive to-do list with Excel (FREE Template + Tutorial) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Do you have an Excel report or graphs that need to be emailed to various people every month? We can use Excel automation features to do this task quite easily.

 

The inspiration – A client request for Excel Email Solution

The idea for this came from a recent project I did for a client. They wanted me to build an Excel workbook which shows latest sales summary and then allows them to email the snapshot to the relevant people in one click.

Here is a snapshot of the solution I created for them (with dummy data):

1. You select a product and see the dynamic report
2. Pick the person who will receive the report (from drop-down list)
3. Click on the “Send email” button to send the email

The ingredients – What we need

Here, I am using 3 main ingredients.

• A report created in Excel 365
• Office Scripts to generate the email contents and trigger the mail process
• Power Automate flow to send the email

Below is a schematic of the whole process.

The Recipe – How to send emails from Excel

The actual recipe is a bit detailed and harder to explain in text only format. So I made a video with the whole thing. Watch it here or on my YouTube channel. I have included the key steps as text below too.

 

https://youtu.be/eeCqaokSSoQ

Scripts & Instructions:

We can send either text or images as the email. In our case, I have both text content and images. The images come from a grouped object named Group 5.

GenerateReport Script:

Go to your “Automate” ribbon in Excel and click on “New Script” button.

In the script window, paste below script and customize the names as needed (refer to the video for explanation on the script).

 

				
					function main(workbook: ExcelScript.Workbook):myOutput {
    // Your code here
    let ws = workbook.getWorksheet("Report");

    let repGroup = ws.getShape("Group 5");

    const repImage = repGroup.getImageAsBase64(ExcelScript.PictureFormat.png);
    const emailSubject = ws.getRange("c2").getText();
    const sendTo = ws.getRange("I22").getText();

    console.log(emailSubject);
    console.log(sendTo);

    return {repImage,emailSubject,sendTo};

}

interface myOutput {
  repImage: string;
  emailSubject: string;
  sendTo: string;
}
				
			

Set up the Power Automate Flow

1. Go to Power Automate website and login with your credentials
2. Create a new instant cloud flow
3. Set the trigger as “When HTTP request is received”
4. Add “Run Script” step in Excel
5. Add Send an email (v2) step
6. Set up the flow as depicted below.

Obtain the Trigger URL

1. Save your flow
2. Now go back the “trigger” step (step 1 of your flow)
3. Make sure you set the method to GET
4. Expand and copy the URL.

Back to Excel to make one more Script

We are nearly done. We just need to add one more script & a button in our sales report so that we can initiate the flow from Excel.

Add one more script in Excel and use the below code.

				
					function main(workbook: ExcelScript.Workbook) {
    // Your code here
    const triggerURL = "___YOUR TRIGGER URL___";


    let request = new XMLHttpRequest();
    
    request.open("GET", triggerURL, false);
    request.send(null);
}
				
			

• Save your script. 
• From “Code Editor” click on the options menu for your script and use the “Add Button” to add a button on Excel worksheet.
• Whenever you click on this button, your flow will start.

Other ways to Automate this:

We can also use VBA to create & send emails automatically. I have previously written about that approach too. Read this article for VBA Excel Email Sender.

 

VBA vs. Office Script approach – which is better?

Both technologies offer automation. I have summarized the pros & cons of each technology below. 

As of 2023 March, my preference is to use VBA for things like Email automation as it is easy to control and deploy. 

VBA

VBA Pros:

• Works in any version of Excel
• Easy to learn, easy to code
• Lots of help & resources
• Very old and stable language base
• Works with Excel, Office & Windows Objects & API

VBA Cons:

• Can't use with Web / Mobile version of Excel
• Not easy to integrate with Cloud platforms (Power Automate, Sharepoint etc.)
• Security problems

Office Script

Office Script Pros:

• Works on Web / Mobile versions too
• Integrates with cloud platforms (Power Automate etc.)
• Future ready technology

Office Script Cons:

• No easy help or resources
• Hard language to learn and master
• Doesn't work in older versions of Excel
• Can't use all objects of Excel. Will not work with Windows API etc. too
• Needs costly subscription plans to use
• Runs on server, thus no control and susceptible to downtimes etc.

Thanks to Mark Proctor

I got the idea for URL trigger from Mark Proctor. Thanks Mark for the fantastic work

 

Got questions?

Do you have any questions reg. this implementation. Post a comment so that our community can help you.

More automation with Excel

• Send Emails from Excel with VBA
• Create Power Point presentations from Excel
• Create Word reports from Excel data
• More VBA articles & tutorials

The post Send Email from Excel with Office Scripts & Power Automate appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Gone are the days when Excel is all about manual intelligence. Now a days, we can summon the powers of artificial intelligence with a click of a button and watch the magic on your spreadsheets.

So in this post, let me summarize some of the key AI features of Excel.

Pattern Recognition with Flash Fill

Need to fill-up some data but follow a smart pattern? May be you have a bunch of email addresses and want to extract the domain names or you have a list of expenses and want to extract the amounts? You can use Flashfill.

Summon it with CTRL+E or just type some data and watch Excel fill up the rest.

See this demo, or read this for more.

Automatic Formulas (Excel)

Want to write a formula but not sure which one would do the work for you? You can just type the data and Excel can figure out the formula logic for you.

Currently available in only Excel online, this feature writes formulas for you. It is called formula by example.

See this demo or read the page for more.

Automatic Formulas (Power Query)

If you need to create a new column of data derived from existing columns, you can use Power Query’s “Column from Examples” option. This is excellent for creating new data without figuring out the M code or PQ steps.

I use it all the time for:

• Creating initials from names
• Extracting parts of the text

See this demo to understand more or read this page to learn more about Power Query.

Automatic AI Analysis

Ever feel the urge to say, “I don’t know how to analyze this data, just do it yourself”. Well, you can now yell that at Excel and it will oblige.

Use the “Analyze data” button in Excel 365 (home ribbon) and let Excel do automatic analysis of your data. It can surprise you

See this demo to understand more.

Recommendations

I am sure if Shakespeare is alive today, he would proclaim that.

All jokes aside, if you are not sure which chart to make or what pivot table to create from your data, you can just use the “Recommend” feature of Excel.

To create a recommended chart:

1. Select your data
2. Go to Insert ribbon and click on “Recommended charts” option
3. Pick one of the recommended choices.

To create a recommended pivot:

1. Select your data
2. Go to Insert ribbon and click on “Recommended Pivots” option.
3. Pick from one of the suggested pivots and insert it.

See this demo or read this article to learn how to pick the right charts yourself.

Forecasting

Need to whip up a forecast from your sales data? Not sure how to do it?

You can ask Excel to study your data and create the forecast for you. Just select your data, go to data ribbon and click on the “Forecast sheet” option.

Boom!

See this demo or learn more about forecasting in Excel.

AI Features of Excel - Video

I made a video about these AI features with many tips & clear explanation. Watch it below or on my Youtube channel.

What is your favorite AI feature of Excel?

Your turn to go.

Tell me what AI features in Excel you like the most? Did I miss something? Let me know thru comments.

The post Top AI features in Excel – a round up appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

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.