A new formula called TEXTJOIN

Tuesday, February 13th, 2018 by admin admin

I was exploring how I could help one of my friends how to join text together that I stumble on this new formula called TEXTJOIN which was added a few months ago by Excel. With the formula, you are able to return multiple values in one single cell. More details can be found in this link https://exceljet.net/formula/multiple-matches-in-comma-separated-list

 

Does the cell contain any of the items in the list

Saturday, February 10th, 2018 by admin admin

One of my friends got an excel list and he wishes to find out in this list whether any of words he has in a second list can be found in the first list. He came out with a formula which is extremely long and find that it is impossible to use the formulas if his second list expands to 200 items. So he shouted for help to come out with a shorter formula. I didn’t come out with one for him but googled the right key words to find somebody has done it. It is in this page https://exceljet.net/formula/cell-contains-one-of-many-things

 

Why you should not use VLOOKUP?

Thursday, February 8th, 2018 by admin admin

I was just pasting some data over from one worksheet to another in the same file. The destination worksheet contains a few thousand VLOOKUP formula. When I paste the data over, it takes quite a few minutes to complete the paste. This is because Excel keep re-calculating the VLOOKUP formulas as the data get pasted over. In this case, you can either turn of auto-calculation which I don’t like to do because I usually forget to turn on after that. The other way is to use MSQuery to link the data together. The third and newer way is to use PowerPivot which can perform the calculation at a faster rate without the re-calculation problem.

Free Excel Course

Monday, January 1st, 2018 by admin admin

If you are computer literate but do not know how to Excel, here is how you can get a Free and Excellent guide to get you started. Here is how you get it.

1. Open Excel or open a new workbook.

2. Click on the icon that says “Take a Tour” as shown in the picture below

new-workbook-interface

3. Go through the functions worksheet by worksheet.

INDIRECT worksheet function

Tuesday, February 2nd, 2016 by admin admin

I have a formula which is used to find out if a particular description is available. It works well except that I have to keep readjusting the range in the formula because it is shortened every time I delete the rows within the range. In the end, I have to make use of the INDIRECT worksheet function to freeze it so that it is not affected by the deleting of rows.

Excel Table

Thursday, January 14th, 2016 by admin admin

Excel Table is a new function in Excel. It can organize information neater compared to a normal Excel worksheets. But one thing Excel Table cannot do is insert cut cells from another row of the table. For example, I would like to swop the values between E5 and E6. The way I would do it is to cut the cell in E6, go to E5 and press Ctrl and + key together. The values will exchange positions. But in Excel Table, this cannot be done.

Paul McKenna: I chose my wife by Excel spreadsheet

Monday, January 4th, 2016 by admin admin

This is not one of the Excel uses I would recommend but Paul definitely used it correctly to organize information and see things clearer.

http://www.telegraph.co.uk/news/celebritynews/12078792/Paul-McKenna-chose-wife-Kate-Davey-by-Excel-spreadsheet.html

VBA Excel – Goto Function

Wednesday, December 30th, 2015 by admin admin

In one of the projects that I was working on, I wrote a set of Macros VBA code to select and highlight the cells that I want to keep. It behaves the same way as when the GOTO function is applied manually. The following code is what I used to highlighted the filtered cells yellow:

            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With

It was working perfectly well until I encounter a case when the filtered result returns zero row. The macros highlighted all the hidden rows instead. After searching high and low for the code to capture this scenario, I finally found that the way to do it is to use the COUNTIF function to count the number of records that the filtered results is likely to generate. This exceptional scenario that I encountered can be captured easily if the COUNTIF function returns the value zero which is zero count. To make it work perfectly in your codes, you need to run the COUNTIF first. If there is more than one count, then go ahead with the GOTO function. The codes to do that is given below. In the code below, I assume that I am trying count how many cells in K2:K100 contain the number 100. If there is at least one, continue with the GOTO Function. If not, skip it.

        xx = Application.WorksheetFunction.CountIf(ActiveSheet.Range("K2:K100"), "100")
        'Highlight cells yellow if auto-filter returns at least one row
        If xx > 0 Then
            ActiveSheet.Range("K2:K" & dlast_row).Select
            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With
        End If

 

A Sum formula that always adds new rows

Thursday, July 9th, 2015 by admin admin

What do you do if you have a list and you constantly have to add the new records to the grand total using the SUM formula? If your SUM formula is immediately below the record, you will have to keep changing your range as the SUM formula would not include the new row added before the grand total row.

One work around is to add in an empty row and include that in your SUM formula. Whenever there is a need to add new records, add it before this blank row so that the SUM formula will always include the new records in the SUM formula.

What if you are not allowed to keep a blank row for this purpose? Today, I am going to show you a new solution.

Assuming that the SUM formula is in A11 and is currently adding the numbers in the range A1:A10. Instead of creating a SUM formula that simply adds up the range A1:A10 [=SUM(A1:A10)], create the range in text format and use the ROW formula. The range including the inverted commas becomes “A1:A”&Row()-1. The ROW()-1 will always calculate one row before the SUM formula. Since this is a text and cannot be read as a range, you need to the INDIRECT formula to convert the text based range into a proper range. Adding the INDIRECT and SUM formula, you new SUM formula should be =SUM(INDIRECT(“A1:A”&ROW()-1)). This new SUM formula will save you the time to change the range constantly and you will not have to live with an empty row between your data and the grand total anymore.

Microsoft Excel can be installed as a standalone Android App

Friday, June 26th, 2015 by admin admin

Previously, you have to install Microsoft Office App (Excel, PowerPoint and Words) to use Excel. Now they come as separate Apps and you can pick the App you wish to install on your Andriod Phone. Watch the YouTube video for more details.

 

s2Member®