ExcelHub - Learn Excel Easily

Excel Tip #004 Reduce file size and load/save time with .xlsb files Using .xlsb files can improve performance if your spreadsheet does a lot of number-crunching. Excel has different options to save a file as an excel file - .xls, .xlsx, .xlsm and .xlsb. The .xls file format used by Excel 2003 and earlier is, internally, a "binary interchange file format"(called BIFF with various suffixes, e.g. BIFF11). Among its limitations, it can store up to 256 columns and 65536 rows. The .xlsx file format, default in Excel 2007, is really just a ZIP file with a bunch of XML files inside. It supports things like a million rows and multithreading optimizations for cell calculation order. This file type is typically smaller than an .xls file with the same contents and is probably faster to open in Excel 2007. The .xlsm file format is an Excel Macro-Enabled Workbook file. Just like with .xlsx files, Microsoft's .xlsm file format uses XML architecture and ZIP comp...

Excel Tip #003 Quickly move around (or) jump to the end of a range Shortcut through keyboard This is one of the shortcut that most of us would know.  Hold down the CTRL key and press one of the arrow keys. This will move to that direction to  the last cell until it encounters a blank cell. Shortcut through mouse Everyone has an assumption that shortcuts are meant to be handled by keyboard but this shortcut is actually easier than the keyboard shortcut. Double-click the edge of the currently selected range in the direction you want to move. When you double-click an edge of a cell, the active cell will jump in that direction to the last cell until it encounters a blank cell. Mouse shortcut to move to the end of a range of cells ADDITIONAL TIP CTRL + HOME - This will activate (or) direct to the first cell in an excel sheet, A1 CTRL + END - This will take to the end of the used cell in an excel sheet. There are ra...

Excel Tip #002 Calculating the difference between 2 dates Calculating the difference between 2 dates is more frequent problem which most of us faced. Below is a solution for the same. We are going to use 2 Excel function in this post -  DATEDIF()  and  NOW() . Let us consider the first problem, calculating the difference between 2 dates where we will use the DATEDIF() function. The syntax of the DATEDIF() function is, =DATEDIF(start_date, end_date, unit) The value fields,  start_date  &  end_date  takes up the date.  unit  field is the time unit that we are going to use - years (y), months (m) or days (d). Consider 2 dates as an example in mmddyyyy format: Our start_date being 7/15/1967 (July 15, 1967) and end_date 12/8/1990 (December 8, 1990). The start_date and end_date are defined in cell B2 & B3 in my excel. To calculate the # of years . To denote the number of years, I have concatenated the text "year...

Excel Tip #001 The use of Text function in getting the day and month from the given date Sometimes we may want to know the day and month of the given date. In such cases, we can make use of the TEXT function to retrieve the information. The formula to show current date is using TODAY() function. =TODAY() The functionality of TEXT function is to convert a value to text in a specific number format. Here we are going to use the same function to convert date into days & months. =TEXT(value,format_text) In the value field, we are going to assign a date and in format_text  field we can define d, m (or) y (date, month & year respectively) based on the output that we are expecting. Let us now consider the date 6/15/2014 (June 15, 2014) which is the value field. This date is defined in the cell B2 in my excel. Assigning format_text "dddd" will return the day of the date.           Formula: =TEXT(B2,"dddd") Assigning form...