Reversing Row order in Excel

/0 Comments/in /by

In many cases we face challenges in reversing row order in Excel.  There are two simple approaches for doing this. One is to add an extra column with serial numbers and then sort on that column in descending order. This works perfect for small amount of data.

For big data it is best to use Microsoft Power Query for Excel Add-in. It works great with Microsoft Excel 2010 and Microsoft Excel 2013.

 

Download Microsoft Power Query for Excel

 

Simple steps to perform the action:

  1. Convert your existing Data to Excel Table. To know more on Excel Table, you can refer to the article Working with Excel Table.
  2. Select the created Table and click the From Table under Power Query Tab as shown in the below image.
  3. The Power Query window will open. Select the Reverse Rows under the Transform Tab.

power-query-tab-table

 

power-query-reverse-rows

Benefit of Power Query?

It works on a smaller sample of data and then applies the transformation when you choose Save and Load option. This is much faster than getting all the data and then trying to sort it which is the first method.

Why is this required?

Usually required with logs where the first transactions or rows are at the bottom. So the data is received in reverse chronological order. Twitter feeds, Timeline Updates, Live blogs – all follow this pattern.

This method works independent of the time-stamp column. What is wrong with timestamp? It may be in different time formats, some rows may have same timestamp and some rows may have no timestamp at all.

Microsoft Excel Version History

/0 Comments/in /by

Microsoft Excel is the largest used Reporting and Data Analysis application now a days. Its important to know the Microsoft Excel version history since the version 1. The table below lists the various versions of Excel for Windows.

VERSIONRELEASEDDESCRIPTION
11985

Version 1, for the Macintosh was released. There never was an Excel 1.0 for DOS or Windows.

21987

Excel 2.0 is the first version of Excel for Intel platform.

31990

Included Toolbars, drawing capabilities, outlining, add-in support, 3D charts, and many more new features.

41992

The first “popular” version. Included lots of usability features.

51993

A major upgrade. Included multi-sheet workbooks. Visual Basic for Applications (VBA) was introduced in this version.

71995

Known as Excel 95. The first major 32-bit version of Excel. This is the first major version after Excel 5.0, as there is no Excel 6.0. Almost no external changes, but faster and more stable.

81997

Known as Excel 97. A new interface for VBA developers, UserForms, Data Validation, and lots more.

This was a major upgrade that introduced the paper clip office assistant and featured standard VBA used instead of internal Excel Basic. It introduced the now-removed Natural Language labels.

92000

Included in Microsoft Office 2000. This was a minor upgrade, but introduced the upgrade to the clipboard where it can hold multiple objects at once. The Office Assistant, whose frequent unsolicited appearance in Excel 97 had annoyed many users, became less intrusive.Can use HTML as a native file format, “self-repair” capability, pivot charts, modeless user forms.

102002

Known as Excel 2002. Included in Office XP. It has a long list of new features, but most of them will probably be of little value to the majority of users. Perhaps the most significant feature is the ability to recover your work when Excel crashes.

This version features product activation technology. Please consider the ramifications of this before deciding whether to upgrade. 

112003

Known as Microsoft Office Excel 2003. The new features in this version are:

  • Improved support for XML
  • A new “list range” feature. Most significant feature in this version
  • Smart Tag enhancements
  • Corrected statistical functions.

Most users will not find the upgrade worthwhile. 

122007

Included in Microsoft Office 2007. This release was a major upgrade from the previous version. Similar to other updated Office products, Excel in 2007 used the new Ribbon menu system. This was different from what users were used to, and was met with mixed reactions. One study reported fairly good acceptance by users except highly experienced users and users of word processing applications with a classical WIMP interface, but was less convinced in terms of efficiency and organisation. However, an online survey reported that a majority of respondents had a negative opinion of the change, with advanced users being “somewhat more negative” than intermediate users, and users reporting a self-estimated reduction in productivity. The new features included in this version are:

  • New Ribbon menu System. This was a major upgrade.
  • SmartArt set of editable business diagrams.
  • Improved management of named variables through the Name Manager
  • Much improved flexibility in formatting graphs, which allow (x, y) coordinate labeling and lines of arbitrary weight.
  • Several improvements to pivot tables were introduced.
  • Office Open XML file formats were introduced, including .xlsm for a workbook with macros and .xlsx for a workbook without macros.
  • Specifically, many of the size limitations of previous versions were greatly increased. To illustrate, the number of rows was now 1,048,576  and columns was 16,384. This changes what is a valid A1 reference versus a named range.
  • This version made more extensive use of multiple cores for the calculation of spreadsheets; however, VBA macros are not handled in parallel and XLL add‑ins were only executed in parallel if they were thread-safe and this was indicated at registration. 
142010

Included in Microsoft Office 2010, this is the next major version after version 12, as version number 13 was skipped.

Minor enhancements and 64-bit support, including the following features:

  • Multi-threading recalculation (MTR) for commonly used functions
  • Improved pivot tables
  • Pivot Table Slicers
  • More conditional formatting options
  • Additional image editing capabilities
  • In-cell charts called sparklines
  • Ability to preview before pasting
  • Office 2010 backstage feature for document-related tasks
  • Ability to customize the Ribbon
  • Many new Function, most highly specialized to improve accuracy 
152013

Included in Microsoft Office 2013, along with a lot of new tools included in this release:

  • Improved Multi-threading and Memory Contention
  • FlashFill
  • Power View
  • PowerPivot Add-In
  • Timeline Slicer
  • Slicer with Excel Tables
  • Ability to Save files in OneDrive
  • Windows App
  • Inquire Add-In
  • Charting Enhancements
  • Recommended Charts and Pivot Tables
  • 50 new functions

VLOOKUP using Approximate Match

/0 Comments/in /by

In a lot of cases, we use VLOOKUP to find exact matches, based on some kind of unique id. But there are many situations where we can perform VLOOKUP using Approximate Match. A classic case is using VLOOKUP to find a Grade for the students where marks are provided.

Suppose we are provided with Test scores of students and have been asked to find the grades for each students base on different slabs of scores. In such cases, we generally use nested IF and the formula goes too long.

We can replace the same with VLOOKUP using Approximate Match by using TRUE or 1 in the last argument that is [range_lookup].

vlookup-using-approximate-match

[range_lookup] is the most critical or we can say the most important argument of VLOOKUP. If we use TRUE, then it finds the closest minimum match from the Master Table.

The most important task to get the desired result is to prepare the table_array in a smart, VLOOKUP friendly format and in the sorted in ascending order.

Download Example Workbook

Excel Error and Related Information

/0 Comments/in /by

While working with Microsoft Excel, we come with various type of error. This Article will guide you through the different error in excel with example and fix for each of these error.

#DIV/0!

This error comes when something is divided with 0.

Example: A Cell with the Formula =15/0 would return in this error.

Fix: Do not divide the value by Zero.

#NULL!

This is rare error. When you use incorrect range operators often displays this error.

Example: The Formula =SUM(A1:A10 C1:C10) returns a #NULL! error because there is no seperator between the two Range.

Fix: Make sure to mentioned the ranges properly.

#VALUE!

This error is seen when you use text parameters to a Function that accepts numbers.

Example: The Formula =SUM(“a”,”b”) returns #VALUE! error.

Fix: Make sure your Formula parameters have correct Data Types. If you are using Functions that work on numbers like SUM, AVERAGE then the parameters should be numbers.

#NAME?

This error comes when something is misspelled in a formula or Named Range. This error will also occur when we forget to close a text in double quotes or omit the range operator.

Example: =SUMM(a1:a10), =sum(range1, UndefinedName), =SUM(a1a10), =GOOD

Fix: Make sure you have mentioned the correct Formula name. In 2007 or later version of Excel, while typing the Formula, Excel shows all the matched ones. In earlier versions of Excel, if we use correct Formulas, they will be automatically capitalized. For example, if you type =sum(1,2,3) in a Celland press enter, it will be changed to =SUM(1,2,3). You can use this way to correct formulas. Make sure you have defined all the Named Ranges you are using in the formula. Make sure any User Defined Functions (UDF) you are using are properly installed. Double check the ranges and string parameters in your formulas.

#NUM!

This error is seen when a wrong data type is supplied in a Function that requires a numeric Argument. This error is also seen when a Function that iterates, such as IRR or RATE, and that Function cannot find a result.The result of a Formula might produce a number that is too large or too small to be represented in Excel.

Example: The Formula=4389^7E+37 returns a #NUM! error.

Fix: Make sure that the Arguments that are used in the Function are numbers. For example, even if the value that you want to enter is $1,000, enter 1000 in the formula. Make your numbers smaller or provide right starting values to your iterative formulas.

#N/A

This is one of the frequent errors you see while using any Lookup Functions. This error is shown when some data is missing, or inappropriate arguments are passed to the Lookup Functions such as VLOOKUP, HLOOKUP, MATCH.

Example: =MATCH(“India”, A1:A10,0). If this formula returns the #N/A error, it means that India is not there in A1:A10 Range.

Fix: Use some Error Handing tricks such as IFERROR. We can modify the above Formula as =IFERROR(MATCH(“India”, A1:A10,0),”Not Found”). This will print Not Found whenever the Formula returns any error including #N/A.

#REF!

This is one of the most common error messages we see when we play with a Worksheet full of Formulas. We get #REF! error when one of the formula parameters is pointing to an invalid range. This can happen because we deleted the cells.

Example: Try to write a forumla like =A1 and then delete the Column A or Row 1. Immediately the Formula returns #REF! error.

Fix: First Undo the actions you have performed. And then rewrite the Formula in a better way.

######

A Cell full of # symbols is seen when the contents cannot fit in the Cell. This is also seen when you format negative numbers as Dates.

Example: A Long number like 12223456345 entered in a small cell will show ####.

Fix: Adjust the Column width. And if the error is due to negative dates, make them positive.

Working with Excel Table

/0 Comments/in /by

An important but often underutilized feature in Excel is tables. This tip describes when to use a table and also lists the advantages and disadvantages.

Understanding what a Table is:

A table is a rectangular range of structured data. Each row in the table corresponds to a single entity. For example, a row can contain information about a customer, a bank transaction, an employee, or a product. Each column contains a specific piece of information. For example, if each row contains information about an employee, the columns can contain data, such as name, employee number, hire date, salary, or department. Tables have a header row at the top that describes the information contained in each column.

You’ve probably created ranges that meet this description. The magic happens when you tell Excel to convert a range of data into an “official” table. You do so by selecting any cell within the range and then choosing Insert ➜ Tables ➜ Table.

When you explicitly identify a range as a table, Excel can respond more intelligently to the actions you perform with that range. For example, if you create a chart from a table, the chart expands automatically as you add new rows to the table. If you create a pivot table from a table, refreshing the pivot table will include any new data that you added to the table.

Below figures shows a range before it was converted to a table and the range after it was converted to a table.

range-of-data-that-is-not-an-official-table

A Range of data that’s not an official Table

range-of-data-that-has-been-designated-a-table

A range of data that has been converted to a Table

Difference between a standard Range and a Range that has been converted to a Table:

  • Activating any cell in the table gives you access to a new Table Tools context tab on the Ribbon.
  • You can quickly apply background color and text color formatting by choosing from a gallery. This type of formatting is optional.
  • Each column header contains a filter button that, when clicked, lets you easily sort the rows or filter the data by hiding rows that don’t meet your criteria.
  • A table can have “slicers”, which makes it easy for novices to quickly apply filters to a table.
  • If you scroll down the sheet so that the header row disappears, the table headers replace the column letters in the worksheet header. In other words, you don’t need to freeze the top row to keep the column labels visible.
  • If you create a chart from data in a table, the chart automatically expands if you add new rows to the end of the table.
  • If you create a name for a column in a table, the “refers to” range for the name adjusts as you add new rows to the table.
  • Tables support calculated columns. A single formula entered in a cell is automatically propagated to all cells in the column.
  • Tables support structured references in formulas outside of the table. Rather than use cell references, formulas can use table names and column headers.
  • When you move your mouse pointer to the lower-right corner of the lower-right cell, you can click and drag to extend the table’s size, either horizontally (add more columns) or vertically (add more rows).
  • Selecting rows and columns within the table is simplified.

Limitations of using a Table:

  • If a workbook contains at least one table, a few Excel features are not available:
  • For some reason, when a workbook contains at least one table, Excel doesn’t allow you to use the Custom Views feature (choose View ➜ Workbook Views ➜ Custom Views).
  • You cannot share a workbook (using Review ➜ Changes ➜ Share) if the workbook contains a table.
  • You can’t insert automatic subtotals within a table (by choosing Data➜Outline➜Subtotal).
  • You cannot use array formulas within a Table.

Generating a List of File Names

/0 Comments/in /by

This tip describes how to retrieve a list of file names in a folder and display them in a worksheet.This technique uses an Excel 4 XLM macro function in a named formula. It’s useful because it’s a relatively simple way of getting a list of file names into a worksheet – something that normally requires a complex VBA macro.

Start with a new workbook and then follow these steps to create a named formula:

  1. Choose Formulas Tab ➜ Define Name to display the New Name dialog box (CTRL + F3).
  2. Type FileList in the Name field.
  3. Enter the formula in the Refers To field =FILES(Sheet1!$A$1)
  4. Click OK to close the New Name dialog box.

filelist_name_manager

Note that the FILES function is not a normal Worksheet Function. Rather, it’s an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and a file specification) and returns an array of file names in that directory that match the file specification. A normal worksheet formula cannot use these old XLM functions, but named formulas can. After defining the named formula, enter a directory path and file specification into cell A1.

For example:

  • F:\Future in Hands\Training\*  to display all files
  • F:\Future in Hands\Training\*xl* to display all Excel files

Then this formula displays the first file found: =INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Below figure shows an example. The path and file specification is in cell A1. Cell A2 contains this formula, copied down the column: =INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. When you change the directory in cell A1, the formulas update to display the new file names.

generating-a-list-of-file-names

Note

If you use this technique, you must save the workbook as a macro-enabled file (with an *.xlsm or *.xls extension).

 

Send Outlook Mail from Excel VBA

/0 Comments/in , , /by

This article features code that you can use to send outlook mail from Excel by using the Microsoft Outlook object model. The below code can be used to send an Outlook mail directly from Excel.

Sub SendOutlookEmail()

Dim OutlookApp As Object
Dim OutlookMail As Object

Set OutlookApp = CreateObject(“Outlook.Application”)
Set OutlookMail = OutlookApp.CreateItem(0)

On Error Resume Next

With OutlookMail

.To = “abc@xyz.com”
.CC = “”
.BCC = “”
.Subject = “This is a Subject Line”
.Body = “This is a Mail Body.”
.Attachments.Add “C:\Report Folder\Test Report.xlsx”
.display

End With

On Error GoTo 0

Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

Note:

  • .display displays the mail with the information provided above. You can use .send instead of .Display to send the mail instantly.
  • Mail can be sent to multiple users. Simply use “abc@xyz.com;def@xyz.com”.
  • Multiple Attachments can be sent by using .Attachments.Add “File Path” in the next line.

Filling Gaps in a Range

/0 Comments/in /by

When you import data, you can sometimes end up with a worksheet that looks something like the one shown in the figure. This type of report formatting is common. As you can see, an entry in column A applies to several rows of data. If you sort this type of list, the missing data messes things up, and you can no longer tell who sold what when.

filling-gaps-in-a-range

If your list is small, you can enter the missing cell values manually or by using a series of Home ➜ Editing ➜ Fill ➜ Down commands (or its Ctrl+D shortcut). But if you have a large list that’s in this format, you need a better way of filling in those cell values. Here’s how:

  1. In Cell A4, type =A3 and press Enter.
  2. Select A4 and Copy the Cell (CTRL +C).
  3. Select the range that has the gaps (A3:A14 in this example).
  4. Choose Home ➜ Editing ➜ Find & Select ➜ Go To Special. The Go To Special dialog box appears.
  5. Select the Blanks option and click OK. This action selects the blank cells in the original selection.
  6. Paste the Data (CTRL +V) and its done.
  7. You can Replace the formula by Paste Special ➜ Values

After you complete these steps, the gaps are filled in with the correct information. Now it’s a normal list, and you can do whatever you like with it – including sorting.

Using SmartArt to create a bulleted list

/0 Comments/in /by

We can use SmartArt to create a bulleted list in Excel. Choose Insert➜Illustrations➜SmartArt, and choose the diagram style from the dialog box.

The below figure shows a “Vertical Bullet List” SmartArt diagram. This object is free-floating and can easily be moved and resized. The example shown here uses minimal formatting, but you have lots of control over the appearance of SmartArt.

using-smartart-to-create-bulleted-list-in-excel

Join multiple text quickly

/0 Comments/in /by

We sometime comes with the challenge to join multiple text in Excel. The first thing that comes into our mind is to use the CONCATENATE Function. The major challenge in using this function that we cannot select a continuous Range such as CONCATENATE(A1:A10). To get the result we need to specify all the cells individually.

To do this quickly and smartly, we can use TRANSPOSE with CONCATENATE Function. Below are the steps for doing this. Let say the cells you want to combine are in A1:A10.

  1. In a blank cell, where you want to concatenate all the values type =CONCATENATE(TRANSPOSE(A1:A10))
  2. Don’t press enter yet.
  3. Select the TRANSPOSE(A1:A10) portion and press F9. This replaces the TRANSPOSE(A1:A10) with its result.
  4. Now remove curly brackets { and }
  5. Press Enter