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…
Category: Articles
Article 43 – Optimize, Troubleshoot and Improve Performance in Excel (non VBA)
Below points will help you in following to optimize your excel sheet Reduce time taken to calculate / recalculate Reduce time taken to open / close workbook 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,…
Article 42 – Generating an Odd Order Magic Square in Excel (VBA)
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 3×3. 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…
Article 41 – Findings from the VLOOKUP and INDEX/MATCH shootout
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…
Article 40 – Order of Operations in Formula
Follow the BODMAS rule B – Brackets (Parentheses) O – Orders (Powers/Exponents) D – Division M – Multiplication A – Addition S – Subtraction
Article 39 – Sorting in Excel
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…
Article 38 – 10 Features I would like to see in Excel
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…
Article 37 – VBA – Generating Unique (Non-repeating) Random Numbers Efficiently
This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100. The algorithm to ensure uniqueness is following – 1. Given lower limit and upper limit, generate all numbers and…
Article 36 – VBA – User Names in Excel
Many times, we are required to fetch User Names in Excel. Unfortunately, getting User Names in Excel is possible only through VBA. These are very small pieces of VBA codes and even a person not knowing VBA can make use of them by following the instructions here. I am going to discuss the various type…
Article 35 – VBA – Timer Function and Overcoming Midnight Limitation of Timer Function
The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in…