On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Friday, January 20, 2017

Arrange and View Multiple Worksheets at Once in Excel

This feature allows you to view multiple worksheets at once from the same workbook at the same time.

 You need to follow beneath simple steps to view multiple worksheets at once from the same workbook at the same time:

  1. Go to the View tab and click New Window



















  2.  Then click Arrange All




















  3. Select either Horizontal or Vertical and put a check next to Windows of active workbook and then click OK.















  4. Result:You will now have two separate views of the same workbook.


















You can view different worksheets in each pane or the same worksheets, it doesn't matter.

Each side is independently controlled. This allows you to avoid flipping between the tabs to compare information.

When you are finished with this view, just close one of the windows and maximize the other one so it fills the Excel window.

Wednesday, January 18, 2017

Count Specific Errors in excel

Learn, How to count the occurrence of a specific error in a Range of cells in Excel.

Follow beneath steps to Count the number of times a specific error appears in a range.

Select the entire range of cells, for which you want to take the count of error occurrence.
 =COUNTIF(A1:A5,"#NAME?")  

This is NOT an array formula.

A1:A5 is the range to check; change this to fit your data.

#NAME? is the error that you want to count. Change this to the desired error and make sure it is surrounded with quotation marks.



Result:
 



Sunday, January 15, 2017

Count the Errors in a Range of cells in Excel

Learn, How to count the number of errors in a range of cells in Excel:

Follow beneath steps to Count all of the errors that occur in a range of cells:

Select the entire range of cells, for which you want to take the count of error occurrence.

 =SUM(ISERROR(A1:A5)*1)

Array formula: you must enter this into the cell using Ctrl + Shift + Enter or it won't work.

A1:A5 change this to your range of data. That's all you have to do to get this to work for your data.


Result:


Thursday, January 12, 2017

Calculate Average Excluding Zeros in Excel

Exclude zeros while calculating the average in Excel. This method removes all zeros from the equation.

We have two methods for Calculation:

Method 1 : For Excel 2007 and later versions: 

Select the range of cells, for which you want to calculate Average and use below function:
 
 =AVERAGEIF(A1:A5,"<>0")  

 A1:A5 change this to the range that you want to average and that's it.

"<>0" is the part that tells the function to ignore cells that have zeros in them.


Result:




Method 2 : For Excel 2003 and older versions: 

If you have Excel 2003 or earlier, you must use this version of the formula. 

Select the range of cells, for which you want to calculate Average and use below function:

 =AVERAGE(IF(A1:A5<>0,A1:A5))

Array Formula - this is an array formula so you must enter it using Ctrl + Shift + Enter.

A1:A5 is the range that you want to average; make sure to change it in both parts of the formula to work with your data.



Result:


If you don't enter this formula correctly, you will see 2.4 as a result.

Popular Posts