Excel Tips (3) with Sanjay Bakshi

Q. If we have a large excel file and wanted to select whole row not just a cell
A. Select Cell and click shift + space Bar

Q. Provide a way to selecting Range of Cell
A. Select first cell and click on F8 selection range process will be activated and then use arrow keys

Q. How can we make column visible as we Scroll
A.  Select the row immediately below the row which we wanted to Freeze
      In the Window menu click the Freeze Panes

Q. You want 00111 to be display but when you enter Excel convert this to 111, what to do here?
A.  Type 00111 in Cell
     Right Click on Cell 
     Select Format Cells
     Select Number TAB
     In category list box select TEXT

   Q. Create reminder process in Excel which will help Doctor to track today's appointment
   A. =IF(E2=TODAY(),"Today's Appointment","")

Q. What is count If and provider one example
A. Count the No of Cells based on Given Criteria. Count if function need two parameter, first is cell range and second is Criteria

Below picture shows a matrix where user wanted to know the no of Employees achieving 100% & Above Target. So in this scenario we will select the cell and in criteria we will write ">=100%". We can use wildcard criteria also in Count If

=COUNTIF(H3:H8,">=100%")

Q. Provide some example of Count If which follow Wild Card Criteria

1) =COUNTIF(D3:D8,"S*") -- This will help user to count the number of cells starting with S

2) Calculate Absenteeism % for one group
    =COUNTIF(F3:F8,"N")/ROWS(F3:F8)

Keep Reading & Keep Calm...

Sanjay Bakshi
mesanjaybakshi@gmail.com