Practical Excel & Power BI Learning Resources

Comparing daily data can be misleading. For example, a Monday usually behaves differently from a Sunday, so comparing them directly may lead to the wrong conclusion. A better approach is to compare the same weekday across different weeks. For example, comparing all Wednesdays in the past few weeks. In this post, I’ll walk through 3 different methods to compare the same weekday across weeks in Excel. Each method can work well depending on the workflow and reporting needs. Thes

Below is a screenshot of time in Second format and in Minutes format. I'm going to show you to convert them into mm:ss format. Second to MM:SS format: First, we need to convert the second to day, there are 86400 seconds in a day. So, what we need to do here, is to divide Second with 86400. After dividing second with 86400, we will get decimal value. At the format panel in Home tab, click on More Number Formats... Then select Custom > mm:ss format > OK. We are done converting

Here's an example of a Pivot Table, and I want to automatically filter the past 30 days whenever I update the raw data and refresh it. So here's the sample raw data of stores sales for the above Pivot Table from May 2024 until June 2024. To make it filter the past 30 days data in the Pivot Table, this is how I do it: At the raw data: Add an additional column to check on whether the date is within the past 30days period. The Formula goes like this: =AND([@Date]>=(TODAY()-30),[

Calendar table is pretty useful as it can link different data sources to produce a consolidated reports or dashboards. For example, in the image below, the calendar table is linked to both transaction data and return data; therefore, both transaction and return for certain product for particular period can be seen in a visual for comparison. Creating dynamic calendar table where the calendar can be automatically updated based on the date information in the raw data source whe

Before diving into the steps for date formatting, we need to understand what is locale. Locale is used to display and identify the data type especially for data such as date, currency and numeric data, different locales have different ways of displaying data. For example, The United States is one of the countries that use MM/DD/YYYY, while in Malaysia, the date format is DD/MM/YYYY. Microsoft Excel or Power Query by default is using the regional setting in your operating syst