COUNT function counts only those cells which are having numbers. Assuming your range is A1:A10, use following formula =COUNT(A1:A10)
Category: Tips and Tricks
Tips & Tricks 15 – Most Frequently Occurring Value in a Range
Assuming, your range is A1:A10, enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put {…
Tips & Tricks 14 – Count Cells Starting (or Ending) with a particular String
1. Say you want to count all cells starting with C =COUNTIF(A1:A10,"c*") c* is case insensitive. Hence, it will count cells starting with both c or C. Suppose you want to find all cells starting with Excel. =COUNTIF(A1:A10,"excel*") 2. For ending =COUNTIF(A1:A10,"*c") c* is case insensitive. Hence, it will count cells starting with both c…
Tips & Tricks 13 – Copy and Paste All Defined Names in the Sheet
You are wondering that if you can copy and paste all defined names in the sheet. Do following 1. Formulas Tab > Use in Formula > Paste Names 2. Click on Paste List to paste all defined names.
Tips & Tricks 12 – Converting Date to a Quarter
Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is 1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4. =CEILING(MONTH(A1)/3,1) OR = ROUNDUP(MONTH(A1)/3,0)
Tips & Tricks 11 – Convert a Number to Named Month
I am deleting this post as I have written a proper article on this and below is a link to this https://excelbianalytics.com/wp/article-24-convert-a-number-to-a-month-name/
Tips & Tricks 10 – Convert from Word Tables to Excel
The best way to do this would be through utilities. The best one which I found out and is free is following – http://watermark-images.com/convert-word-to-excel.aspx Another way is to copy Word tables one by one (or in one go if layout permits) into Excel – http://office.microsoft.com/en-in/excel-help/copy-a-word-table-into-excel-HP010254130.aspx
Tips & Tricks 9 – Circular Error is Coming..How to find where is the problem
You are working with your spreadsheet and all of a sudden, you get following error – To know where is the problem, click OK on this. Go to Formulas > Error Checking > Circular Reference This will list a problem. Work on this problem cell and remove circular reference. If there are more problem cells,…
Tips & Tricks 8 – Change Order of Legends in a Chart
1. Click the chart and click on Select data. 2. Here in the left pane, you can click on a legend name and make it up and down through click on up / down arrows.
Tips & Tricks 7 – Breaking Long Formula in Multiple Rows
While writing the tip on Removing Numbers from String, I encountered this problem. My formula was too long to be contained on the page. Hence, I had to span it in many rows. To do this, you just need to press ALT+ENTER at the place where you want to continue the formula to a new…