DataCleer

In this blog post, we will explore VLOOKUP function in Excel, and see how they can help you find the information you need. The VLOOKUP function is one of the most basic and widely used lookup functions in Excel. It allows you to search for a value in the first column of a table, and return a value from another column in the same row. The syntax of the VLOOKUP function is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) The arguments of the VLOOKUP function are: Table Argument Explanation lookup_value The value that you want to find in the first column of the table. This can be a cell reference, a text, a number, or a logical value. table_array The range of cells that contains the table of data. The first column of this range should contain the lookup value, and other columns should contain the return values. col_index_num The column ...

Logical functions are functions that allow you to test conditions and return values based on the results. In this blog post, we will learn how to use nine common logical functions in Excel: IF, AND, OR, XOR, NOT, IFERROR, IFNA, IFS, and SWITCH.  IF Function The IF function allows you to make decisions based on whether a condition is true or false. The IF function can also be nested inside another IF function, to test multiple conditions and return different values.  Syntax of the IF function is: =IF(logical_test, value_if_true, value_if_false) Student Data Example I have also applied conditional formatting for cells containing "F" values AND Function The AND function checks multiple conditions and returns TRUE if all of them are true , and FALSE if any of them are false. You can have up to 255 conditions in the AND function.  Syntax of the AND function is: =AND(condition1, condition2, …)  Employee Data Example OR Function The OR function checks multiple conditions an...

In the previous blog post, we learned the  Basics of Pivot Table  in Excel. In this blog post, we will explore some of the advanced features of pivot tables, such as slicers, timelines, calculated fields, pivot charts, managing relationships, etc. These features can help you change your existing pivot table and create different views of your data every time. Slicers Slicers are used to filter your data by selecting specific values from a field. For example in this data table , we have a column named Product (Crayons, Geometry Box, Paper, Pen, Stapler, Sticky Notes)  you can use a slicer to show only the data for a particular product or products.  Steps to add a Slicer: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the field for the slicer. A slicer window will appear, where you can select a field and a slicer will be inserted. You can select one option or multiselect by holding down the CTRL key while making your selections. You c...

Pivot tables are one of the most powerful and useful features in Excel. Allowing us to quickly summarize, analyze, and explore large amounts of data in various ways. It is a special type of table that displays the summary of a data set and helps us in analyzing the data in seconds without knowing any formulas and functions. It consists of four areas: rows, columns, values, and filters. You can drag and drop different fields from your data set into these areas to create different views of your data. In this blog post, we will introduce the basics of pivot tables. Converting data to tabular data before inserting Pivot Table is an added advantage as this eliminates the need to frequently adjust the table range as new entries are added (select a cell from the data and click CTRL+T and then OK).  Additionally the following conditions need to be fulfilled: ✔ Each column should have unique headers, and the headers should be descriptive enough.       For ex. If Revenue...

In this blog post, we will learn one of the most important part of DAX: time and date functions. These functions allow you to work with dates and times in various ways, such as extracting, formatting, aggregating, comparing, and filtering. You can use these functions to create dynamic and interactive reports that show trends, patterns, and insights over time. Time and date functions Time and date functions are DAX functions that deal with dates and times. They can take values from a column containing date or time and give single value or a table as an output. Time and date functions are useful for several reasons: create calculations based on dates and times format and display dates and times in different ways compare and filter dates and times such as finding the latest or earliest date How to create a date table in Power BI? A date table is a special table that contains a list of dates, such as year, month, quarter, week, day, etc. It is essential for time and date calculations in Po...

DAX (Data Analysis Expressions) is a powerful language for creating and manipulating data in Power BI. It allows us to perform calculations, transformations, and analysis on our data. We can use DAX to create measures, calculated columns, calculated tables, and more. In this blog post, we will explore some of the basic DAX functions and formulas that we will use in Power BI. We will cover the following topics: Common DAX functions and DAX operators Common DAX functions DAX has a lot of functions that we can use for various calculations, such as aggregation, filtering, logic, text, date and time, and more. DAX syntax is the set of rules that define how to write DAX formulas. DAX syntax is case-insensitive, meaning that we can use uppercase or lowercase letters. Aggregation functions Aggregation functions are used to perform calculations on a set of values, such as sum, average, count, min, max, etc. Some of the aggregation functions are: SUM: Returns the sum of the values in a column or...

In this blog post, I will explain what filtering and row context are, how they affect your calculations, and how to use them correctly. I will also show you some examples and tips to help you understand these concepts.  Filtering Context in Power BI Filtering context is the set of filters that are applied to your data before DAX starts to calculate your formulas. These filters can come from different sources, such as: Slicers, visuals, or report filters that you use to interact with your data Relationships between tables that you create in your data model Filter functions that you use in your DAX formulas, such as FILTER, CALCULATE, or ALL To use filtering and row context in Power BI, you need to understand how they interact with each other and with your DAX formulas. Filtering context always applies before row context. This means that filtering context filters the table first, and then row context selects one row from the filtered table. NOTE: You can modify the filtering context ...

In this blog post, I will explain what SUM and SUMX are, how they work, and when to use them. I will also show you some examples and tips to help you understand these functions. What is SUM? SUM is a simple aggregation function that takes a column of data and adds the values to give a total. It works like the sum function in Excel, but Excel works on cell references or cell ranges and DAX works on columns.  = SUM(ColumnName) For better understanding refer example no. 1. What is SUMX? SUMX is a more complex function that takes a table and an expression as inputs and returns the sum of the expression evaluated for each row of the table. It allows you to perform calculations or logic on each row of a table before summing up the results.  = SUMX(Table, Expression) For better understanding refer example no. 2 and 3.   Differences between SUM and SUMX Features SUM SUMX Input Accepts only a single column. Takes a table and an expression. Syntax ...