Created by : Chetna Sharma, Delhi (India)

Logical Question and Answer.

New Excel,  VBA, MS Access,  SQL, SAS and PowerBI batch starting from Saturday, contact 8319560164 for more details.

Click LINK for more Details.

Q1 - What is Excel? Explain various feature of its?

  • Excel is an electronic spreadsheet.
    • We can summarized huge amount of data in simpler way.
  • Automatic Calculation.
  • Unique address of each cells.
  • Graphical interface.
  • User Friendly

WhatApp 8319560165 your feedback and better answer or any new question.

Q2 - Explain different versions of Excel we have?

Main Excel which are in Market or I worked in are 2003,2007,2010,2013,2016.

Most recent Excel in market is 2019.


Q3 - How many Rows and Columns we have in Excel?

Version                    Rows                     Columns


2003                        65536                    256


2007 and above     1048576               16384


Q4 - Explain type of sheets in Excel?

There are 3 type of sheets in Excel.

  1. Worksheet (Sht F11)
  2. ChartSheet (F11)
  3. MacroSheet (Ctr F11)

 


Q5 - What is difference between Count and CountA?

Count           :- Count the number of cells in a range that contain pure number.

CountA        :- Count the number of cells in a range that are not Empty.


Q6 - What is difference between Average and AverageA?

Average     :- Sum of all numeric values/Count of all numeric values.

AverageA  :- Sum of all numeric values/Count of all Non Empty cells.

 

 

 


Q7 - What do you understand by PivotTable in Excel?

Pivot table is the most powerful feature of Excel, we use this feature to summarize and rearrange selected columns and rows of a dataset to attain desired report. When you create Pivot table, it doesn’t change the dataset or source data itself.


Q8 - How to decrease size of Excel file?

below are few points to decrease file size :-

  1. Convert Excel File into Binary form (Excel Binary Workbook) (Extension : .xlsb)
  2. Press Delete button on blank rows and columns.

Q9 - What do you understand by StaffHub?

StaffHub is a Microsoft product, which make easy for managers to develop, modify and manage shift details, streamlining what has been a Employee salary, incentive, bonus etc.


Q10 - How we can add new sheet in workbook?

Shit F11


Q11 - How can we increase number of rows and columns in Excel?

No, we cant increase number of rows and columns in Excel.

Q12 - How we can disable Autofit in pivot table?

To disable AutoFit, we have to right click on Pivot goto PivotTable Option then in Layout & Format tab, uncheck ‘Autofit column width  on update’

Q13 - How to check the modification of pivottable?

WhatApp your answer on 839560164

Q14 - How to improve performance of Excel file?

  1. Use less number of formula in excel.
  2. Always keep Excel workbook in manual mode and press F9 for calculation when ever is needed.
  3. Copy paste formula if no more needed.

Q15 - Why we use function and formula in excel?

We use function and formula to improve performance and save our time of calculation.


Q16 - What is difference between function and formula?

Function :- Microsoft has developed some predefined logic to populate data from different data set are called functions.

Formula :- combination of function is called formula which which combined multiple output.


Q17 - How we can move in different sheet in excel?

Ctr+PgUp – To move Right to Left.

Ctr+PgDown – To move Left to Right.


Q18 - What is difference between RED and GREEN triangle at top cell indicate?

RED       :- Red triangle indicate some comments in cells.

GREEN:- Green triangle indicate some specific formatting or unclean data in a cells


Q19 - What is fastest way to go any cells or range?

Press F5 or Ctr+G

Option ‘Go To’ and write your cell address or Range address in reference as per below snapshot.


Q20 - What is difference between ActiveX control and Form control?

Form controls are Excel features and ActiveX are loaded from DLL.

We can add/remove ActiveX but form controls are fixed in Excel.


Q21 - Difference between PivotTable and Subtotal?

Pivot table create unique records and find the apply aggregated function on it.

In SubTotal does filter records and find apply aggregated function on it.


Q22 - Explain Match, Find and Search function in Excel?

Match :- Match function is used to find position of one item in an range.

Find :- Find function is used to find position of one item within a cell (case sensitive).

Search :- Search function is used to find position of one item within a cell (Not case sensitive).


Q23 - How we can make PivotTable data source dynamic?

We can make PivotTable Data source dynamic with help of ‘Define Name Range‘ and ‘Offset function‘ in Excel.


Q24 - How we can merge Excel Cells?

Coming Soon 🙂
Mobile : 8319560164(WhatApp Only)
Email : ExcelVBATips@gmail.com

Q25 - Difference between Calculate Item and Calculate Field?

Calculate Item and Calculate Field are features of Excel Pivot table. when we are planing to do some calculation with in Pivot table we use those option.

When we try to find aggregated difference between to headers(Fields) of data in pivot table we use Calculate Fields and when we need to calculate 2 or more items of data of any field we use calculate items.

Refer Question 15 and 16 from link.


Q26 - What are limitation of Calculate Item, how we can Remove them?

Whenever we apply ‘Calculate Item’ to any field of pivot table it increases the Grand total of Pivot table, in this case we have to add customized (Manually) Grand Total in Pivot.


Q27 - How to Find Character Values in a given Range?

Toggle content goes here, click edit button to change this text.

Q28 - What is difference between SumIf, SumIfs, Sumproduct, Dsum function?

Sumif :- Review link for more details

  • It has single sum Range.
  • it works on single column single criteria.
  • It has 3 Parameters
    • =SUMIF(range,criteria,sum_range)

SumIfs :- Review link for more details

  • It has single sum Range.
  • It works on Multiple column and in each column we can give single criteria.
  • It has 255 Parameters, one sum range, 127 Critera_Range and 127 criteria.
    • =SUMIFS(sum_range,criteria_range,criteria,…)
    • =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,………….criteria_range127,criteria127)

SumProduct :- Review link for more details

  • It has multiple sum Range but it will multiply them.
  • It works on Multiple column(255) and in each column we can give multiple criteria with (+) operator.
  • Its have 255 parameters.
    • =SUMPRODUCT(array1,array2,array3,…array255)

Dsum :-

  • It has Singe sum Range.
  • It works on Multiple column and in each column we can give multiple criteria but have to manage Criteria in Excel with headers same as we have in Data Range.
  • Its have 3 parameters.
    • =DSUM(database,field,criteria)

 


Q29 - Difference between Subtotal and Aggregate function?

SubTotal :- Review link for more details.

  • It is used aggregate function used to calculate filtered data.

Aggregate :- Review link for more details, It is a advance version of SubTotal Function with some new features.


Q30 - Difference between 'And' and 'Or' function?

And :- Review link for more details.

  • It is logical function and it combine small logic to provide consolidated logic.
  • All Logical test must be TRUE.
  • It has 255 parameters
    • =AND(logical1,logical2,………….logical255)

OR :- Review link for more details.

  • It is logical function and it combine small logic to provide consolidated logic.
  • Anyone condition must be TRUE.
  • It has 255 parameters
    • =OR(logical1,logical2,………….logical255)

Q31 - Difference between Large and Rank function?

Large Function :- Review link for more details.

  • It return the largest value you specify in a set of values.
  • It has 2 parameters
    • =LARGE(array, k)

Rank Function :- Review link for more details.

  • It give the position of value in set of values
  • It has 2 parameters
    • =RANK(number, ref, order)

Q32 - Difference between Slicer and Timeline in Excel?

Slicer and Timeline are need features added in Excel 2010 and above version.

Slicer :-

  • It is sub feature of Pivot and Table in Excel.
  • It can be applied in any format of data.
  • It is alternate of report level filter.
  • It is used to filter entire data of Table or Pivot Table.
  • It automatically create unique list of column you specify.

TimeLine:-

  • It is sub feature of Pivot and Table in Excel.
  • It required column of data should be formatted as DATE.
  • It is alternate of report level filter.
  • It is used to filter entire data of Table or Pivot Table.
  • It automatically pick Year/ Quarter / Month/ Day if you have a single DATE of that period.

For more details please review question 28 and 29 from Link.


Q33 - What are different type of reports format available in Excel?

There are 3 type of report format available in Excel.

  1. Tabular Form.
  2. Compact Form
  3. Outline Form

Q34 - Difference between Subtotal and Grandtotal?

Sub Total :- The aggregation of one set of a values from large group of number is called subtotal.

Grand Total :- The final/Total amount after everything is aggregated is called Grand total.

For better understanding create a Pivot Table and 2 Fields in row part, and any field in Data/Values field. out of 2 field in Row Level first will create subtotal of each items and at bottom you will find grand total.

Q35 - What are limitation of Vlookup?

Below are few basic limitation of Vlookup Function in Excel.

Syntax ~ VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  • Rule 1 :- Table_Array must be in vertical form, so Vlookup never works on horizontal Table.
  • Rule 2 :- Lookup_Value should always be in first column of table_array or we can say it must be in left most column of table_array.
  • Rule 3 :- Lookup_Value should be less then or equal to 256 characters.
  • Vlookup always works from Left to Right.
  • It always show first match from table_array.
  • For Range_lookup (Approximate match) table_array must be shorted in ascending order of lookup value columns.

Review below links for more details.


Q36 - Difference between Index and Offset?

Index :- Review link for more details.

  1. Index is used as advance version of Vlookup.
  2. Index have its limited Range (Table_Array or Array).
  3. It is used to hit array function(CSE function) in excel.
  4. Syntex :- there are 2 syntex in Index function but primary we use
    • =INDEX(array,row_num,column_num)

Offset :- Review link for more details.

  1. It is used for dynamic ranges.
  2. It don’t have range limitation, it can move in any direction in within Excel sheet.
  3. Syntex :- =OFFSET(reference,rows,cols,height,width)

Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q37 - Difference between Networkday, workday and Weekday?

NetWorkDay :- Review link for more details.

  1. It is used to calculate net working days between two days, excluding Saturday and Sunday long with holiday list.
  2. Syntex :- =NETWORKDAYS(start_date,end_date,holidays)

WorkDay :- Review link for more details.

  1. It is used to calculate Projected date from current date after adding n number of days, excluding Saturday and Sunday long with holiday list.
  2. Syntex :- =WORKDAY(start_date,days,holidays)

WeekDay :- Review link for more details.

  1. It return week number of current date, by default week starts from Sunday.
  2. Syntex :- =WEEKDAY(serial_number,return_type)

Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q38 - How to find difference in Year, Month and days between two dates?

To find difference between 2 dates in Year or Month or Days we use DateDif Function in Excel. Review link for more details.

Syntex :- DateDif(Start_Date, End_Date, “Format”)

Format we use as below :-

“Y” – Total number of years between to dates, including Saturday and Sunday.
“M” – Total number of Months between to dates, including Saturday and Sunday.
“D” – Total number of Days between to dates, including Saturday and Sunday.
“YM” – Months after excluding all complete years from 2 dates, including Saturday and Sunday.
“MD” – Days after excluding all complete years and months from 2 dates, including Saturday and Sunday.

Please help me in making it better, 
Kindly share your feedback on 8319560164
Or write us on ExcelVBATips@gmail.com

Q39 - How many 'If function' we can apply in single cell?

We can apply 64 nested If function in a singe cell in 2007 and above versions


Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q40 - Give an Example of Vlookup Approximate match? What is primary rule for this?

Primary Rule of Vlookup Approximate  match is the Column respect to lookup value in  table_Array must be sorted in ascending order.

We can take the example of employee grade with respect to Salary.

Q41 - Difference between Match and Vlookup?

Match :- Match function return the position on items that matches in Single column or Single row.

 

Vlookup :- Vlookup return the value of items in same row that match in table.


Q42 - What is PivotChache?

PivotChanche is a memory that store data in imagery form and and which is further used to create pivot table.

Data of pivot chache is visible once you double click on any value field of pivottable.

Q43 - How we can create single PivotTable from multiple data source?

Q44 - Difference between PivotTable and PowerPivot?

Below are few difference between PivotTable and PowerPivot.

  1. The biggest difference in PivotTable and PowerPivot is Performance, PowerPivot performance is much better the PivotTable.
  2. PivotTable is a Excel Functionality where as PowerPivot is Add-ins introduced in Excel version 2010 and above.
  3. PivotTable is created from Excel Data so it is limited to 1.048576 rows only where in PowerPivot we can utilized data upto 2 GB.
  4. In PowerPivot we can use multiple source of data, where in PivotTable it is limited to single source (Except multi consolidation ranges)
  5. In PowerPivot (KPI) we can create Pivot with Iconic presentation where PivotTable gives data summary only.

Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Review PowerPivot in details.

Q45 - Explain PowerPivot, PowerView and PowerMaps in Excel?

PowerPivot :- Review PowerPivot in details.

Microsoft introduced a new powerful feature in excel 2013 and 2016. PowerPivot is an add-in we can use for data analysis.

  1. Excel Data Model helps you to create your models.
  2. It allows you to use multiple data sources, you can import data from many sources like a database, text files, Excel itself etc.
  3. You could import, merge and perform analysis from multiple data sources together.
  4. DAX (Data analysis expressions) make it more powerful by adding new calculated columns in data Model.
  5. In PowerPivot we don’t have any restriction of data size.

PowerView :- Review PowerView in detail

PowerView is a used for data visualization and is available in Excel 2013 and above version.
It help us in creating good and attractive data presentation through Charts, Graphs and Maps.


PowerMap :- Review PowerMap in detail

PowerMap is 3D presentation of data, we can create 3D time visualisation of data visualization in globe. We can present our data into geographical way on a globe and create animated charts on globe.


Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q46 - What is data validation?

Data validation is a feature available in Microsoft Excel that is used to restricts the values in one or more cells or range.


Q47 - How we can may dynamic validation depends on value of other column?

By defining the each value of first validation list and them call them in second validation list by ‘Indiret function‘.


Q48 - What is char? Explain different type of charts in excel?

Toggle content goes here, click edit button to change this text.

Q49 - What is waterfall charts?

Toggle content goes here, click edit button to change this text.

Q50 - Explain paretochart?

Toggle content goes here, click edit button to change this text.

Q51 - Explain type of cell format available in excel?

Toggle content goes here, click edit button to change this text.

Q52 - What do you understand by data model in Pivottable?

Toggle content goes here, click edit button to change this text.

Q53 - What are ribbons in excel? Name few them?

Toggle content goes here, click edit button to change this text.

Q54 - What do you understand by wrap text?

Toggle content goes here, click edit button to change this text.

Q55 - How we can select all formula cells in excel?

To select formula in given range follow below steps.

  1. Press ‘Ctr G’ or ‘F5’
  2. Click ‘Special…’
  3. Click Formula  and Press OK

Q56 - How we can select all blank cells in sheet?

To select formula in given range follow below steps.

  1. Press ‘Ctr G’ or ‘F5’
  2. Click ‘Special…’
  3. Click Formula  and Press OK

Q57 - What is use of consolidator in excel?

Toggle content goes here, click edit button to change this text.

Q58 - how we use name box?

Toggle content goes here, click edit button to change this text.

Q59 - How we can project Workbook and Worksheet?

Toggle content goes here, click edit button to change this text.

Q60 - How we can prevent someone from copy cells or range?

Toggle content goes here, click edit button to change this text.

Q61 - How we can make Sumproduct as count function?

Toggle content goes here, click edit button to change this text.

Q62 - what do you understand by quicktoolbar in excel?

Toggle content goes here, click edit button to change this text.

Q63 - Why Dynamic Dashboards as so important now a days?

Toggle content goes here, click edit button to change this text.

Q64 - How we can make quickest dashboard in excel?

Coming Soon – 8285052214 🙂

 

Email ID – ExcelVBATips@gmail.com

Q65 - How can we provide dynamic range to any function in dashboard? and why it is important?

Coming Soon – 8285052214 🙂

 

Email ID – ExcelVBATips@gmail.com

Q66 - Can we hide formula applied in any cells? How?

Toggle content goes here, click edit button to change this text.

Q67 - How you can prevent user from editing cells in dashboard?

Toggle content goes here, click edit button to change this text.

Q68 - How to apply password in Excel to open or to open read only?

Toggle content goes here, click edit button to change this text.

Q69 - Can we apply filter and create pivot-table in protected sheet?

Toggle content goes here, click edit button to change this text.

Q70 - Explain type of file format available in Excel Name them?

Toggle content goes here, click edit button to change this text.

Q71 - What is criteria limitation of CountIf and Sumif function in Excel?

its 15 charters.


Q72 - What do you understand by Wild Card in Excel?

+

*


Q73 - What do you understand Array Formula in Excel? what do you understand by CSE function in Excel?

CSE (Control+Shift+Enter)stands for Array formula in Excel.


Q74 - Difference between IF and IFS function in Excel?

.

 


Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q75 - Difference between TextJoin and Concatenate function in Excel?

.

 


Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q76 - Difference between MaxIfs and MinIfs function in Excel?

.

 


Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q77 - What is difference between .xlsx and .xlsb files?

.

 


Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q78 - What is difference between Vlookup and Sumif?

.

 


WhatApp 8319560165 your feedback, better answer or any new question.

 

 

Q79 - What is thumb rule for Data Validation?

Data Validation always highlight logical value ‘FALSE’


WhatApp 8319560165 your feedback, better answer or any new question.

 

Q80- What is thumb rule for Conditional Formatting?

Conditional formatting always highlight logical value ‘TRUE’


WhatApp 8319560165 your feedback and better answer or any new question.