Category: Formulas

Difference between SUM and AUTOSUM

Formulas, Tutorial, Trainings

Hi there,

All excel users will be aware of how to use sum and autosum feature. First let me tell you, SUM and AUTOSUM functions are one and the same. This is what conceptionally thinking by everyone. However it is actually not.

If you want to defend with my statement, you can definitely, but for now proceed with me and by the end of this post, you will actually join your hands with me.

Difference between SUM vs AUTOSUM


USING SUM FUNCTION:

Full story »

Find SomeOne in SomeThing (excel tip)

Formulas, CF

Hi there,

 

Christmas is nearing, I think most of you are all busy in shopping, right? Great!. Keep enjoying with your new plans for this christmas. And, as every body know, Christmas is the last festival in this year to celebrate so, most of you all celebrate by spending lots of money by giving lot of gifts to the nearest and dearest ones as santa surprise. Finally, this festival is very special to many of you like many people in this world. 

 

However, lets proceed to the topic now.

 

RawData to work on, one cell value to find it in another cell

 

The topic for today is, finding the cell value of one cell in the another cell which contains additional information apart from that one cell value.

Full story »

Shortcuts Used in Formulas

Formulas, Shortcuts

Hi there,

 

This post will provide you the keyboard shortcuts used while we are working on Formulas in Excel. 

 

 Keyboard Shortcuts   What it Does? 
F4   Switch between Absolute /Relative References 
F9   Recalculate the work book
CTRL + '   Copy the formula from above cell
SHIFT + F3   Formula Search Option
SHIFT + F9   Calculate active worksheet
CTRL + ALT + SHIFT + F9   Calculates all workbooks after rechecking the dependent formulas
CTRL + A   After opening the sqare brackets use CTRL + A to display its arguments
TAB   when you type a letter, corresponding functions will display, select the appropriate and hit tab to activate that function
)   We can skip the last closed paranthesis because Excel automatically takes it.
= or +   We can start the formula either with the ''is equal to" or "plus" symbols, always.
F5 + ALT + S +F   Use this shortcut to select all the formulas in the worksheet 
CTRL + SHIFT + ENTER   To create an array formula
CTRL + ~   This shortcut shows all the formulas in the worksheet.

Full story »

Life History of INDEX

About, Formulas, Arrays

Hi there,

 

Today, while I was browsing through google to learn new techniques on Excel, I have found a great article on INDEX function. Once you read this complete post, you will definitely say thanks to the writer. Further delaying let me share the words of the writer. Makesure you carry a meal and tea to your desk before you start reading this post, because it is bit lengthy. :)

 

The Excel INDEX function is the single most important in the roster of Microsoft Excel functions.


Now that might be surprising considering the function's humdrum name, but please pay close attention, because INDEX is one of the magical secrets of how to use Excel! So what's so great about the INDEX function? It's nonvolatile, sprightly, agile, and versatile. Excel INDEX can return one value or an array of values; it can return a reference to one cell or to a range of cells. INDEX works well on either side of the three Reference Operators - the colon, the space, and the comma.

Full story »

Create Dynamic DropDown using Excel DataValidation

Formulas, Learn Excel

Hi there,

 

Today, you will be mastering on how to create dynamic dropdown's using data validation feature in Excel. First question your mind throws out is what is Dynamic Drop Down (DDD) right? Yes, let me start answering to it, first.

 

Say, we have 3 countries with few number of states for each country. This data needs to be updated in an Excel when you are working on country wise data or state wise data. So, updating the matching states to each country is quite a tidious process and lot of remembering power is required. 

Full story »

Find Top 3 Sales Using Top 3 Formulas

Formulas

Hi there,

Today, I am sharing a great tip which will automatically updates the top 3 sales made by your team every time when you update the raw dump. I have done this earlier using a complicated formulas in the classes but after seeing this method. I thought I want to share it with you to "make you passionate on excel". 

How to List TOP 3 Sales from the RawDump using Excel

Let's move on to complete this mission impossible 4 !!! :)


Download your working file before you proceed.

Full story »

Contact / Help. ©2019 by Abhilash VK. Design & icons by N.Design Studio. Skin by Tender Feelings / EvoFactory.