EXCEL TIPS AND TRICKS

 SEARCHABLE DROPDOWN LIST

=FILTER(A2:A32,ISNUMBER(SEARCH(D1,A2:A32))


VLOOKUP: TO MATCH THE DATA WITH THE TEXT IN THE TABLE AUTOMATICALLY

  Application of VLOOKUP:

To compare two sheets for values and Extract data from them.

=IFERROR(IF(C13<>0,VLOOKUP(C13,'RB-1'!$C$5:$M$44,11,0),""),"")

  • if error→ eliminates #NA display
  • If condition→ checks for blank cell and display the data extracted by VLOOKUP  which matches C13 table in given sheet at 11th column. If incorrect display nothing. 0 means exactly at the same row.  

INDEX function: Extracts data from the table address denoted by Match Function

MATCH Function: Shows the address of the cell, not data, containing the data. 

Compare Two sheets For differences:

 For example, if you sent the quotation and the clients send the last edited sheet for agreement, compare the one you sent and they said they agreed. Sometimes, they twist the words within. 

One option we have is to view side by side. 

Other is using the Formula. 

searchable dropdown Lists

=IFERROR(VLOOKUP(ROWS($E$3:E3),D3:E43,2,0),"")

INDEXMATCH


SUMIF:

IF the column consists of the same data repeated over and over, and you have to add their quantity which lies in a different column together, This function is used. One column act as a Source range and the other as a sum range. 


dependable dropdown list


TODAY'S DATE AUTOMATIC: =TODAY()→ TODAYS DATE EVERYDAY, AUTOMATICALLY UPDATE BY FORMULA

CTRL+; → TODAYS DATE: JUST TO GET VALUE OF TODAY. TOMORROW IT WON'T UPDATE





data entered in 11/30/2021



-- sign changes true false to 1 and zero .true or false is due to is number condition.




IF FOUND

_ _ISNUMBER(SEARCH($M$15,G14))

FREQUENCY

IF(H14=1,COUNTIF($H$14:$H14,1),"")

SEARCHABLE

INDEX($a$2:$a$88,match(rows($C$2:$C2),$C$2:$C$88,0)

keep neat and tidy

IFERROR(INDEX($G$14:$G$21,MATCH(ROWS($I$14:$I14),$I$14:$I$21,0)),"")



Highlight differences between 2 sheets with conditional formatting

To highlight cells that have different values in two sheets with the color of your choosing, use the Excel conditional formatting feature:

  • In the worksheet where you want to highlight differences, select all used cells. For this, click the upper-left cell of the used range, usually A1, and press Ctrl + Shift + End to extend the selection to the last used cell.
  • On the Home tab, in the Styles group, click Conditional Formatting > New rule, and create a rule with the following formula:

    =A1<>Sheet2!A1

Comments

Post a Comment

Popular posts from this blog

Electrical Load- Transformer allocation

Image
    ELECTRICAL LOAD CALCULATION CHILLER PLANT 0.5 kw  per ton  is an “annual average  chiller -plant efficiency.” For Example, 300 TR,  power consumption in kW= 324*.5    = 1.5 In the below Example, 300 TR of chiller Consumes the 193 kW of Energy. The water cooled version has a full load efficiency of about 0.7 kW/Ton. The air cooled version has a full load efficiency of about 1.1 kW/Ton. For example, if the system had a pump with a known efficiency of 65 percent and the pump delivers 250 gallons per minute to a total head of 72 feet, the horsepower of the system is: Hp = \frac{250\times 72}{3,960\times 0.65} = 6.99\text{ horsepower} H p = 3 , 9 6 0 × 0 . 6 5 2 5 0 × 7 2 ​ = 6 . 9 9  horsepower In the above Example,  efficiency is 80%. H p = 3 , 9 6 0 × 0 . 6 5 816×50 ​ =15.85  Hp =15.85X.746 =11.8 kW Chiller Cooling Tower: 350 TR cooling tower Fan consumes 22.38 kW. Rule: 6 CFM per Watt AHU:  375 CFM PER TON Electrical POWER C...
Read more

Efficiency Booster- For anything

Are you tired of trying to hard trying to save project or goals from falling apart? I was. So, I have come up with some ideas that will help in managing the stress retaining the efficiency your work demands. Below I have listed some of them. If you have some, let me know in the comment section. 1. To make any work efficient,  Break down the work into many smaller steps.   2. Which steps are repeated often? Try to automate or use easier tools for the work. 3. Which Steps requires more time and attention? Use efficient tools and manpower. 4. There are some items or actions that may seem insignificant but the work cannot be moved forward without them.  5. Make the decisions regarding the things that take a long time. For example, if the project needs airplane after some months and You need to order from others. Otherwise, the decisions will linger and it will be out of our control.  6. Create work schedule for the works and break down the work for example, if the p...
Read more

Think Like a Designer-Hotel Building (How they think, Decide)

  GUIDE: HOTEL BUILDING                                                                                                                                                                             -Harihar Poudel Experience-With Experts Today I am writing my experience as an Engineer. I got a chance to have a meeting with Hotel Design and Architecture Experts on how to make the hotel efficient. Though, I am not an architect, as an engineer I am naturally Curious how they th...
Read more