There’s has been lot of questions related to performance when it comes to sub-query or joins. Which one is better and when to use which, it always create mass confusion to developer. Today, lets check which is better performance wise and how to use it. Here are the queries: 1 2 3 Query 1: SELECT…
All Excel users know how to use VLOOKUP and how much useful it is. But most of the excel users has never used INDEX & MATCH functions in excel. Both have certain features that make it superior to VLOOKUP in many situations. INDEX MATCH is designed for vertical lookups, which is the task that VLOOKUP…
Today we will discuss about how to increase the performance of ASP.NET application and the answer is CACHING. Caching is a technique of storing frequently used data/information in memory, so that, when the same data/information is needed next time, it could be directly retrieved from the memory instead of being generated by the application. Why…
Today we will learn how to create custom functions in excel, although excel has large range of inbuilt functions which are good enough for our daily usage. Need for custom function arises when we want to calculate / perform task which excel’s inbuilt functions does not support. A UDF (User Defined Function) is simply a…
The Pivot Table is one of the most powerful features in Excel. It is an interactive way to quickly summarize large amounts of data. A PivotTable report is useful to summarize, analyze, explore, and present summary data. So let’s get started with how to create Pivot table. Our dataset consists of 15 rows and 6…
A circular reference is created when we refer to same cell either directly or indirectly. A circular reference can have a significant impact on performance because it can iterate indefinitely. Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. By Default, Iterative calculations are turned off in excel. Common…
Today we will learn about cell references, cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. Following are the three types of cell references Using…
Text to Column is a feature in excel from which we can separate text in a cell with comma, spaces, tab or any other special characters into multiple columns. Let’s see an example to understand how it works. I have list of names with commas in column A, refer the below screenshot. 1. Select the…
Today in this article, we will see VLOOKUP & HLOOKUP which are the most useful, yet most mis-understood function in Excel. Function searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. The V in VLOOKUP stands for…
In today’s article, we will see how to create dependent drop-down lists in excel. When we select one of the Countries, we get the list of cities of the respective country in other drop-down. To create these dependent drop-down lists, execute the following steps. 1. On second sheet, create the following named ranges. For creating…
S&P Infotech Blog 