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…
Category: Tips and Tricks
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…
Tips & Tricks 6 – Assign a Shortcut Key to a Symbol
In many scenarios, you may be using a symbol very often. For example, new Indian Currency Symbol ₹. Every time, you need to insert symbol which is many steps process and introduces inefficiency. You can use following method to assign a short-cut key to a symbol. (Below example is actually not a short cut key but…
Tips & Tricks 5 – Allow Entry of Dates if they are not Weekends
1. Select the Cell > Data Tab > Data Validation > Data Validation 2. Under Settings Tab, in Allow, select Custom 3. Put following formula =WEEKDAY(A1,16)>2 4. Customize your Input and Error Message as per your requirements.