Sun 30 Jul 2017

Article 47 - New Functions in Excel 2017

By |Sunday, July 30th, 2017|Categories: Articles|Tags: , , , , , , , , |0 Comments

This is a guest post contributed by Hannah Sharron of  http://spreadsheeto.com/

Microsoft has added six new built-in functions with the release of Excel 2016. These functions are also available in Office 365. In this article, we will take a quick look at three of those new functions.

TEXTJOIN

For a long time, the CONCATENATE has been a standard for many users as a method for joining data strings together. However, with the introduction of TEXTJOIN, Microsoft had refined this process even further.

(more…)

Sun 09 Jul 2017

Article 46 - Creating Pivot Table with Dynamic Range

By |Sunday, July 09th, 2017|Categories: Articles|Tags: , , , , , , , , |1 Comment

The file related to this article can be downloaded from Dynamic Pivot Tables

We all make pivot tables and we also know that every time, the range of data which pivot uses goes beyond the current range, we need to change the data range. It becomes painful and also if you are creating dashboards, it is a poor design. Once you create a dashboard, anybody should be able to refresh the pivot and not worry about changing ranges.

(more…)

Sat 20 May 2017

Article 45 - How to know if a Cell contains Time

By |Saturday, May 20th, 2017|Categories: Articles, VBA|Tags: , , , |0 Comments

We talked about Article 19 – How to Check if a cell contains a date. Now, in this article, I will talk about checking the cell for time.

The way Excel stores dates as numbers, in the same way it stores time as numbers only. Only change is in range. The numbers get stored between 0 to 1. Hence,  6 AM is stored as .25, 12 PM is stored as 0.5 and so on.

(more…)

Sun 26 Mar 2017

Article 44 - Not able to open .xls file after upgrading to Excel 2016 / Windows 10

By |Sunday, March 26th, 2017|Categories: Articles|Tags: , , , , , |0 Comments

The problem of .xls file not getting opened after upgrading to Excel 2016 / Windows 10 is a common problem. This problem may occur after any windows / MS Office update as well.

1. Start the Excel and CTRL+O (or File > Open) > Locate the file and open the file. This should open the file but this  may not solve the problem of double clicking the file and opening it.

2.A quick repair usually fixes the file association

https://support.office.com/en-NZ/Article/Repair-an-Office-application-7821d4b6-7c1d-4205-aa0e-a6b40c5bb88b

3. If this doesn't fix the association, when you right click on .xls file > Open With > You should get the list of programs which can open this file and you should select Excel.

In case, the list of program doesn't have Excel, then choose BROWSE and select Excel.exe. But, in case the folder which you get after clicking BROWSE doesn't have Excel.exe, you will need to find the location of Excel.exe to select Excel.exe. To find the path, you can refer to below link -

http://eforexcel.com/wp/tips-tricks-147-find-the-location-of-excel-exe/

1

4. If there is a file block issue

4.1 Right Click on the file > Properties > In the General tab, you will get Unblock option down below > Check the unblock box

4.2 If you don't get unblock option, ALT+T+O (or File > Options) > Trust Center > Trust Center Settings > File Block Settings > Uncheck the box in front of .xls file

1

Fri 18 Nov 2016

Article 43 - Optimize, Troubleshoot and Improve Performance in Excel (non VBA)

By |Friday, November 18th, 2016|Categories: Articles|Tags: , , , , , , , |0 Comments

Below points will help you in following to optimize your excel sheet

  1. Reduce time taken to calculate / recalculate
  2. Reduce time taken to open / close workbook
  3. Reduce file size

Below points may not be valid in many circumstances so you will have to see the suitability of a recommendation but most of the time, these will be valid. Also, in this article we would not concentrate on “WHY” but “WHAT”.

(more…)

Sat 13 Aug 2016

Article 42 - Generating an Odd Order Magic Square in Excel (VBA)

By |Saturday, August 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , , , , |1 Comment

A magic square needs no introduction and we come across it many times. A magic square is a square grid and the minimum size of a magic square is 3x3. The whole numbers in magic square appear only once and all cells are filled. The horizontal rows, vertical columns and main and secondary diagonals all add up to the same number. This number is called magic constant. The more about magic square can be read here - https://en.wikipedia.org/wiki/Magic_square

(more…)

Sat 16 Jul 2016

Article 41 - Findings from the VLOOKUP and INDEX/MATCH shootout

By |Saturday, July 16th, 2016|Categories: Articles|Tags: , , , , , , , , , |0 Comments

Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time.

Following is the methodology to test -

1. The worksheet 100000 contains 100001 records. Column A is Numbers and column B is Text. Column C is alpha-numeric which is the result field.

(more…)

Sat 18 Jun 2016

Article 40 - Order of Operations in Formula

By |Saturday, June 18th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , |0 Comments

Follow the BODMAS rule

B – Brackets (Parentheses)
O – Orders (Powers/Exponents)
D – Division
M – Multiplication
A – Addition
S – Subtraction (more…)

Sat 21 May 2016

Article 39 - Sorting in Excel

By |Saturday, May 21st, 2016|Categories: Articles|Tags: , , , , , , , , , , |0 Comments

First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort).

The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values > Blanks

(more…)

Sat 16 Apr 2016

Article 38 - 10 Features I would like to see in Excel

By |Saturday, April 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would like Excel to have.

1. Making Volatile Functions Non-volatile on the basis of a Parameter

We all like TODAY(), NOW(), RAND() and RANDBETWEEN() (there are other volatile functions also). They are very useful but suffer a fatal flaw, if something gets recalculated, they always get recalculated also. Hence, if I am using TODAY() to derive today's day and if I open the sheet tomorrow, TODAY() will change tomorrow's date. I wish that all volatile functions should carry a parameter so that they should not recalculated when the sheet is recalculated. For example, we should have something like TODAY(N), NOW(N),RAND(N) and RANDBETWEEN(N) where N
(more…)