Tips & Tricks 115 – Insert Fixed Current Date and Current Time
We all are aware about today() and now() formulas which insert current date and current date/timestamp. But these change with every recalculation of your worksheet. But if you want to enter the current date and time which doesn't change with recalculation i.e. it gets fixed, then following Excel shortcuts can be used. Current Date –…
Article 28 – How to Unhide all Tabs (Worksheets)
If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same. Option 1 – Use Custom Views 1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the…
Solution – Challenge 26 – Find Sum given multiple OR conditions across columns
Below is a possible solution to the challenge Challenge 26 – Find Sum given multiple OR conditions across columns. Put following formula for the result – =SUMPRODUCT((COUNTIF($F$1:$I$1,$A$2:$A$20)+ COUNTIF($F$2:$I$2,$B$2:$B$20)>0)*($C$2:$C$20)) The workbook illustrating the solution can be downloaded from Solution – Challenge 26 – Find Sum given multiple OR conditions across columns.
Challenge 28 – Create Pascal's Triangle
Pascal Triangle is very famous in number theory. Interested persons can read about Pascal's triangle at following Wikipedia article – https://en.wikipedia.org/wiki/Pascal%27s_triangle Anyway, whether read it at Wikipedia or not, below is Pascal's triangle. One look at it and you will understand the pattern. Now, the challenge before you is following – 1. Put 1 in…
Excel Quiz 23
Tips & Tricks 114 – COUNTIF for non-contiguous range
All of us love COUNTIF. And it is very easy to do – just say =COUNTIF("A1:A100",">5") and it finds all the values within the range A1 to A100 which are greater than 5. But what if I wanted the result for only A3, A8 and it should omit other cells. Try putting in following formula…
Tips & Tricks 113 – Drag and Drop a Picture in Excel Sheet
Try dragging and dropping a picture in a worksheet – What happens. You will notice a plug sign which means that a picture will be added. But when you release the cursor to drop the picture in Excel sheet nothing happens. Reason – Excel doesn't support dragging and dropping the picture in a worksheet. Now,…
Solution – Challenge 25 – Prepare a Project Status Chart with Sliders
At the outset, let me thank How-to Make an Excel Project Status Spectrum Chart from where I learnt this kind of chart and inspired to set this challenge. My challenge is almost the same except that this is not spectrum but three distinct range of RAG are there. Below is a possible solution to Challenge…
Article 27 – Remove Leading Apostrophe
Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more…