Skip to content (Press Enter)
  • Home
  • Services
  • Blog
  • Case Studies
  • About Me
Macrordinary

Macrordinary

  • Home
  • Services
  • Blog
  • Case Studies
  • About Me
Home>excel

Tag: excel

Updated on November 18, 2020November 18, 2020/ Brent Allen

Make a KPI Card like Power BI – In Excel!

Make a KPI Card like Power BI – In Excel!

In Power BI, one of the frequently used visuals is a key performance indicator, or KPI card. It allows to quickly hone in on key values for your dashboard. To avoid overwhelming users with incredibly high values, numbers are also …

Read More
Updated on February 13, 2020February 13, 2020/ Brent Allen

Using XLOOKUP and TRANSPOSE

Using XLOOKUP and TRANSPOSE

With XLOOKUP , it is now possible to return multiple values from a single function. However; when these values are returned, they show in multiple columns. If we want to have them appear by row, we have to use the …

Read More
Updated on January 25, 2020January 2, 2020/ Brent Allen

Power Query – Clean data for the Data Model

Power Query – Clean data for the Data Model

In previous exercises, we used the FILTER() function in order to remove the totals from our data model. Using Power Query, clean data is provided to the data model. Filtering before the data model improves the performance of the model, …

Read More
Updated on December 19, 2019December 20, 2019/ Brent Allen

INDEX / MATCH – How does it work?

INDEX / MATCH – How does it work?

Up until the release of the XLOOKUP function, there has been significant debate in the Excel community around which function is better – VLOOKUP, or INDEX / MATCH. But, what is INDEX / MATCH? This is a combination of 2 …

Read More
Updated on December 15, 2019December 9, 2019/ Brent Allen

FILTER and CALCULATE – Removing pre-calculated totals.

FILTER and CALCULATE – Removing pre-calculated totals.

In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the …

Read More
Updated on December 5, 2019November 22, 2019/ Brent Allen

Sum of a row using CALCULATE()

Sum of a row using CALCULATE()

In the last post, we created a custom field in the Excel data model; however, we still needed to use Excel formulas to calculate the percentage of a row. In this example, we will be using a simple CALCULATE() function …

Read More
Updated on November 27, 2019November 13, 2019/ Brent Allen

Custom Field for Quarterly Data in the Data Model

Custom Field for Quarterly Data in the Data Model

As part of the Quarterly Report case study, we built a report using the data model, but there were still some instances where we used Excel formulas. In this post, we will be creating a custom field so that there …

Read More
Updated on November 4, 2019October 5, 2019/ Brent Allen

SUBTOTAL() vs. SUM()

As an accountant, one of the frequent tasks that needs to be accomplished is the summing up of results; this can be anything from summing up ledger balances to a full income statement or balance sheet. We normally use a …

Read More

Recent Posts

  • LET Function in Excel: A Name Parsing Example
  • Make a KPI Card like Power BI – In Excel!
  • Top N Results from Excel using Dynamic Arrays
  • DA-100 – The Certified Data Analyst Exam
  • Ranges in Office Script – They are different than VBA!

Categories

  • dashboards
  • excel
  • financial formulas
  • formulas
  • M
  • office script
  • pivot tables
  • power apps
  • power bi
  • power pivot
  • power platform
  • power query
  • scripts
  • VBA
Copyright © 2021 Macrordinary. Software Company | Developed By Rara Theme. Powered by WordPress.