What is Data: 2 types of data and their general understanding

What is Data

Data are basically units of information that are usually numeric. Technically it is a set of values of qualitative or quantitative variables of a person(s)/subject(s) usually collected by observation.

Data is raw, unorganized which needs to be processed, organized, structured and to be presented in a given context to make it useful to be called as information.

Data could be primary or secondary depending on source of origination.

What is data
What is data

In a high level categorization we can bifurcate data as Qualitative and Quantitative

What is Qualitative data type

Qualitative data type are mostly in categorical form is non numerical and is available in textual and non descriptive form like rating “very satisfied”, “Yes/No”, “Male/Female”, “Observation/review of … Continue Reading

Excel Slicer: How to use Excel slicer to filter data

Excel Slicer: How to use Excel slicer to filter data

What Slicer in excel

Slicers are floating buttons in excel by which we can filter data in an excel table or pivot table. In addition to this slicer reflects the current filter state means what options are selected currently. We can move slicers anywhere in the sheet and even can change their size and color.

How to create Slicer

To create a slicer we need a pivot table or just an excel table. First, we need to go to the Insert tab in ribbon >> Filter section >> Slicer as shown in the below image.

Slicer 1
How to create Slicer

Click on Slicer a new pop up option will come … Continue Reading

What is Spark lines in Excel and How to use it in 3 different ways?

What is Spark lines in Excel

Sparklines also known as micro charts adds rich visualization to tabular data without taking much space. This bit size graph can fit in a cell and can provide powerful insight.

Where to find Sparklines in Excel?

Go to Excel sheet >> Insert >> Sparklines

What are sparklines in excel
How to find Sparklines

How many Sparklines available for use?

There are three Sparklines available in excel. Line, column and Win/Loss.

Line Sparkline: It is the first sparkline and can be used with high number of data points like date.

Go to sparklines tab in Insert option and click Line, we will get a pop up like below.

Sparklines 2
Line Sparkline Creation

We will select F7 to AB7 in Date RangeContinue Reading

How to use XLOOKUP in excel: Understanding it with 3 examples

How to use XLOOKUP in excel

XLOOKUP is used to search a range or an array and then return a value matching to the condition irrespective of the column direction. It is an extension of vlookup wherein we could get value from one direction only. XLOOKUP has more flexibility with itself as compared to vlookup.

Let us understand the syntax of XLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments of XLOOKUP:

ArgumentsDescription
lookup [Required]The value to search for.
lookup_array [Required]The array or range to search.
return_array [Required]The array or range to return.
not_found[optional] Value to return if no match found.
match_mode[optional] 0 = exact match (default),
-1 = exact match or next smallest,
1 = exact match or next larger,
2
Continue Reading

How to add Developer Tab in excel | adding developer tab in excel

How to add Developer Tab in excel

The Developer Tab in excel is a very important tab to accomplish many tasks in excel. It contains Macro-related buttons, Add-ins, Controls, and XML. The Developer tab is not preloaded in excel.

How to load Developer tab:

Go to File >>Option

How to add Developer Tab in excel
Developer tab

Then Go to Customize Ribbon >> Check Developer button

Cutom ribbon
Custom Ribbon

A new option will be added in ribbon as a developer:

Developer ribbon
Developer ribbon

We can see for different blocks in developer tab which is very useful in report creating.

You can also read below article for more info.

https://www.excelcampus.com/vba/enable-developer-tab/

https://www.excel-easy.com/examples/developer-tab.htmlContinue Reading

VBA in Excel: What is VBA, How to use

VBA in Excel

VBA stands for Visual Basic Application. It is an event-driven programming language used by Microsoft for its applications. VBA helps in many way ways like creating an automation process of any activity that we do manually, creating user-defined functions, or any activity which is repetitive in nature. By using VBA in excel we can save time and resources.

How to use VBA in Excel

We can use it in two different ways either by using VBA editor or by recording Macro. The choice depends on the expertise and comfort level of the user.

Where is VBA in excel

To use VBA editor we need to open VBA editor either by pressing alt & F11 or go to … Continue Reading

What is Power Query in excel: how to use Power Query in excel

What is Power Query in excel

Power Query in excel helps in connecting multiple sources and sizes of data. It helps in defining an automated process while connecting the first time and this can be used multiple times to save time and resources.

Excel Power Query is a business intelligence tool to perform extract, transform and load functions in Excel/Power BI. It has a graphical interface to load data and Power Query Editor helps in transforming data in a desirable way.

How to use power query in Excel

To use power query in excel we need to go in ribbon and select Data option. We will get first options as Get & Transform Data. In this option we get … Continue Reading

How to use INDIRECT function in Excel

How to use INDIRECT function in Excel

In this blog post, we will try to understand about INDIRECT function of excel. The excel INDIRECT function returns a reference to a range. We can use this function to create a reference that won’t change if rows or columns are inserted in the worksheet. Or, cause it to create a reference from letters and numbers in other cells.
The INDIRECT function in excel has two arguments as seen in the below picture:

How to use INDIRECT function in Excel
Indirect function in excel
  1. ref_text: A cell reference or text string (or both), that creates the range reference. The referenced range can be a cell, a range of cells, or a named range.
  2. a1: TRUE or FALSE. Does the
Continue Reading

How to Get Stock Market Data Into Excel

How to Get Stock Market Data Into Excel

In this blog post, we will learn how to get stock market data in excel. In many situation, we need share market data for further analysis to make some decisions. We have different sources for importing share price data but in every scenario, we get this data in CSV format and then copy it in some excel and this process is very time-consuming. For better time management I have created a tool for importing share price data by which we can fetch stock market data for daily, weekly, and yearly time frames. This tool uses yahoo finance as source data .

In the below snapshot of the tool, we can see that … Continue Reading

How to use MATCH function in Excel

How to use MATCH function in Excel

In this blog, we will be discussing MATCH function in excel. MATCH function basically helps in finding the position of a value. It will not fetch any number but will provide only the position of the lookup value in excel. This function is very helpful when it works with other functions like OFFSET/INDEX.

Let us understand the anatomy of excel MATCH function. It requires 3 input values as can be seen in the below picture.

How to use MATCH function in Excel
match function excel
How to use MATCH function in Excel
How to use MATCH function in Excel

match_type can be explained as below:

match function option
Match function option

In example 1: we will try to find out the position of USA in the country … Continue Reading