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.
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.
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.
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.
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
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 -
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
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, these will be valid. Also, in this article we would not concentrate on “WHY” but “WHAT”.
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
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.
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