Showing posts with label Excel Tutorials. Show all posts
Showing posts with label Excel Tutorials. Show all posts

How to split first and last names in Excel 2013


Download Excel File

Conventionally, the "convert text to columns" wizard was generally used to split first name and last name in earlier versions of Excel.

A new feature called "Flash Fill" has been introduced in Excel 2013 to perform such task. Flash Fill detects a pattern in your initial data entry which enables it to figure out remaining data you want to enter
The main advantage of using Flash Fill is that there is no need of any formula, wizard or any kind of construct to get the desired output.

Suppose we have full name of the person in column A. We are planning to split this name as below:
First name – Column B
Last name – Column C
Following steps will help to split first and last names into separate columns:
  1. Enter the first name in cell B1 and press ENTER
  2. Start typing the next one and instantly the "Flash Fill" feature will show the remaining list of first names
  3. Hit ENTER to accept the suggested list OR press "Esc" to continue typing remaining names
  4. In the similar way, initiate to type Last name in cell C1 and press ENTER
  5. Again, Flash Fill will give suggested entry once you initiate typing in second entry
  6. Press ENTER to accept suggestion from Flash Fill and it's done
Important note: Flash Fill feature is case sensitive. If you type lower case for initial entry, all the suggested entries will be in the same case. This is as such one more advantage over using "convert text to columns" wizard.

Bonus tip: If Flash Fill is turned on but doesn't start automatically when you type data that matches a pattern, you can try starting it manually by clicking Data >>> Flash Fill, or by pressing Ctrl+E.

Two-way lookup in Excel 2013 using INDEX and MATCH

Two-way lookup gives value of the intersection of desired row and column values in a table containing data.

For example, suppose we have data as per table below, and we are looking for number of bank transactions of David in the month of August. This requirement is appropriate to go for 2 way lookup.

i.e. We are looking up value at intersection of desired customer name row (David) and desired month column (August)

To lookup the desired name of customer (e.g. David), we will use MATCH function. This function returns the relative position of particular value in the given cell range or an array.

Syntax for MATCH function:
MATCH (lookup_value, lookup_array, [match_type]

lookup_value: the value whose relative position is required
lookup_range: the range in which the relative position is to be found
[match_type]: put "0" for exact match

Thus, we will get relative row number of the customer by using MATCH function.

Similarly, to get relative column position of desired month we will use MATCH function.

Now, we have relative position of both row as well as column in the data table. To lookup the output value we will now use INDEX function.

Remember, the INDEX function gives ouput corresponding to given relative row number and column number in the data table.

Syntax for INDEX function:
INDEX (array, [row_num], [col_num])

array: the defined range of cells or an array to use in the lookup (basically the table with data)
[row_num]: relative row number in the defined range of cells
[col_num]: relative column number in the defined range of cells

Please note that "array" would not contain the row and column headings.

Thus, now the story is pretty clear. We need to feed the MATCH function used to fetch relative row number in place of [row_num] in INDEX function. Similarly, feed the MATCH function for relative column number in [col_num].

The final formula will look something like this:

How to insert current date and time in a cell in Excel 2013

Download Excel File
Current date and time in Excel 2013

When we work in Excel, we sometimes require to enter current date or time. The purpose may be to maintain log of activities or tracking some changes.

There are mainly two methods to insert current date & time in a cell in Excel 2013:     
1. Dynamic – A date or time that updates when the worksheet is recalculated
2. Static – A value in worksheet that doesn't change
Inserting "DYNAMIC" date and time
TODAY and NOW functions are generally used to insert dynamic current date and time respectively.
Syntax for the TODAY function is:
=TODAY()
NOW function is used to insert "current time" which is dynamic
Syntax for NOW function is very similar to TODAY:
=NOW()
Important Note: As you might have noticed, these functions have no arguments i.e. () does not contain anything. As a result, the data updates each time the worksheet is recalculated.
Inserting "STATIC" date and time
Select the cell into which you want to insert current date or time
To insert current date >>> Press CTRL + semi-colon (;)
To insert current time >>> Press CTRL + SHIFT + semi-colon (;)
To insert both date and time >>> Press CTRL + semi-colon (;), then press SPACE and then CTRL + SHIFT + semi-colon (;)
Important Note: In both the above cases, Excel will give date format as per the default settings. You can change the format from cell format menu [Shortcut: "Ctrl+1"] in "Number" tab >> Date

How to change name in cell comments in Excel 2013

Inserting cell comment
Quick recap on the process of adding a cell comment
Select cell >>> Right click >>> Click "Insert Comment"

After adding the cell comment, if we hover over the cell:
  1. The cell comment is visible with the name of the Author on the top of comment box
  2. Name also appears in the status bar at the bottom of the Excel
Sometimes its good to change the display name on the cell comment. Imagine you are doing some work on behalf of your boss and copy is going to the client!!
Continue reading, to see how you can quickly change the display name in the comments:
  1. Click on the "File" tab and click "Options"
  2. Select "General" category in the same
  3. Under the heading "Personalize your copy of Microsoft Office" you can see "User name"
  4. Edit the box besides "User name" to add desired Author name
  5. Press OK and it's done.
You can try inserting a new comment and see that the author name has changed.
Important note: The existing cells with cell comment before changing username will have no effect and will have the same author name as before.

How to center headings without merging cells in Excel 2013

Merged cells are annoying at times and create many problems while using various functions. The best practice suggests that merged cells should be avoided as far as possible.
Common issues with merged cells:
  • Tools such as pivot tables, data tables etc. are difficult to use with merged cells
  • Sorting and filtering the data is difficult
  • Using various commonly used excel functions like SUMIF becomes difficult
General motivation behind using merged cells is to create multi-column headings.
Let's see an alternative approach to create multi-column headings without merging cells in Excel 2013.
Enter the heading on the top of one of the columns
To center the given heading over the rest of the adjacent columns:
1. Select the cells till you want to center heading
2. Go to "Alignment settings" button on the Ribbon
3. Under "Horizontal" >>> Click drop down arrow >>> click "Center Across Selection"
4. Click OK.
Visually, this does exactly the same as Merge & Center but importantly, it does not merge the cells.
Alternatively, using shortcut buttons:
  1. Type "Ctrl+1" to open "Format Cells" dialog box
  2. Press "A" to go to "Alignment" tab
  3. Press "Alt+H" for "Horizontal" drop down
  4. Select "Center Across Selection"
  5. Press OK.
Shortcut summary: Ctrl+1 >>> A >>> Alt+H >>> Center Across Selection >>> OK
Complete the Formatting part
You can now format the headings with common outside border and apply different fill colors to it.

How to password protect workbook in Excel 2013

Password Protect workbook in Excel 2013
There are primarily two levels of password security you can apply to workbook in Excel 2013:
  1. Protection for structure
  2. Protection for Windows
Protection for Structure
This feature disables workbook structure features like insert, delete, rename, move, copy, hide or unhide
To protect workbook for structure
Open workbook >>> Go to "Review" tab >>> Click "Protect Workbook"
[Keyboard shortcut: Alt +RPW]
Click on "structure" and enter desired password. Press OK.
Then re-enter the password to confirm the same. Press OK.
Protection for Windows
This feature protects the workbook windows i.e. one cannot move or change size or close windows
The process to enable this is very similar to protecting the structure.
Open workbook >>> Go to "Review" tab >>> Click "Protect Workbook"
Click on "Windows" and enter desired password. Press OK.
Then re-enter the password to confirm the same. Press OK.
Unprotecting workbook
To unprotect the workbook, click on Protect Workbook again (on "Review" tab) and enter correct password.

How to compare two lists of data in Excel 2013

Suppose we have to compare two lists of data as given below. Basically we want to find which elements in first list is not present in second list and vice versa.
To highlight the data which is not covered in other list follow below steps:
  1. Select first data range A2:A17
  2. Go to "Home" tab >>> Conditional Formatting >>> Click "New Rule…"
  3. Select the option "Use a formula to determine which cells to format"
  4. Enter the formula =COUNTIF ($B$2:$B$25, A2) = 0
  5. Go to "Format...” to select required highlighting format and click OK
Explanation: The COUNTIF function applied here will count all the instances of A2 in entire second list, if the same is not found, the count will be zero and hence the entry will be highlighted. Similarly, it will check for all the instances in first list.
Similarly, we can highlight cells in second list which are not in the first list by following steps:
  1. Select second data range B2:B25
  2. Go to "Home" tab >>> Conditional Formatting >>> Click "New Rule…"
  3. Select the option "Use a formula to determine which cells to format"
  4. Enter the formula =COUNTIF ($A$2:$A$17, B2) = 0
  5. Go to "Format...” to select required highlighting format and click OK

How to use RANK function in Excel 2013

How to use RANK function in Excel 2013
The RANK function returns the rank of a number, i.e. its position within a list of numbers in Excel 2013. We will also look into RANK.EQ and RANK.AVG functions which are introduced in Excel 2013.
Syntax: RANK (number, ref, [order])
  • Number: the number whose rank needs to be found
  • Ref: list of numbers from which the rank is intended to be found
  • Order: (Optional) specifies whether to rank in ascending or descending order
If order = 0 (or omitted) >>> Excel ranks number as if the list was sorted in descending order
If order is any nonzero value >>> Excel ranks number as if the list was sorted in ascending order
Note: For "Ref", any non-numerical value or reference would be automatically ignored by Excel 
Caution: Make sure to freeze the "Ref" if you want to drag the cells to Rank multiple values
Important note: In case of ties (i.e. in case more than one value has same rank), this function returns same rank and skips subsequent rank.
E.g. If two values have rank 2, then the next entry will have rank as 4 (rank 3 will be skipped and two entries will be given rank 2)
Excel 2013 has two separate functions for RANK
  1. RANK.EQ – This returns same rank and skips subsequent rank as mentioned above.
  2. RANK.AVG – This takes average of ranks and subsequently gives average rank to all ties (e.g. ties at rank 2 and 3 will give rank as 2.5)

How to count blank or empty cells in Excel 2013 using COUNTBLANK

The COUNTBLANK function is used to count empty cells in Excel 2013

Syntax:
=COUNTBLANK(range)

Arguments:
The range can be cell references or can be directly entered.

Follow the below steps to count cells that are not empty:

1. Enter the data in the worksheet

2. Select any cell where you want to get the count of blank or empty cells

3. Enter a COUNTBLANK formula and select the required cell range as argument

4. Press the Enter key.

This will give the count of the empty cells in the selected range

Note: The COUNTBLANK function will NOT count cells with formulas (even if they were value pasted later) and spaces

For example, formula like =IF(A1="","", "na") may have inherent "" value to signify empty cell and it may appear to be blank, but COUNTBLANK function will exclude this in its count.

Add-on:
To check if the cell contains any character (which looks empty), you can use =LEN(cell) function. This should ideally give 0 if the cell is empty.

However, the "" (often after value pasting the formula) does not get detected, to see this we need to check mark "Transition navigation keys"

File >>> Options >>> Advanced >>> Lotus Compatibility

How to highlight Duplicates in Excel 2013

Is there an easy way to locate and highlight duplicates in a list in Excel?
Yes, Excel has many powerful tools to locate and highlight duplicates in a list or data range. Conditional formatting is one of the tool with which we can highlight duplicates.
Let's learn the process of highlighting duplicates with the help of an example:
1. Select the cell range where you want to find and highlight duplicates
2. Use the Conditional Formatting tool located in "Home" tab of the ribbon as mentioned below:
Conditional Formatting >>> Highlight Cells Rules >>> Duplicate Values [Shortcut: Alt+HLHD]
This will activate the "Duplicate Values" dialog box.
3. Select "Duplicate" from the dropdown in the dialog box and also select relevant highlight option
 To apply any customized format you can select "Custom Format..." from the dropdown list. The preview of format will appear while you are selecting from the dropdown.
4. Click "OK"
This will apply the desired format to duplicate values in the selected range.
IMPORTANT NOTES:
  • The feature is NOT case sensitive.
  • Excel will recognize the cells as duplicate only if both cells contains exactly same data.
  • In case any extra spacings are present in any one of the cells, Excel will not recognize them as duplicates (If both cells have same extra spacings, then it will recognize as duplicates!!)

Creating drop down list in Excel 2013 using data validation

Validating your data while entering data into the cell helps you to prevent unnecessary/invalid data in the input cell. Excel 2013 provides great tools to ensure the validation of input data in a given cell.
Drop-down lists or combo boxes helps in picking up a value from a valid list to enter in a cell.
Let's learn to create drop down list in Excel with the help of an example:
  • Creating a drop down menu list using Data Validation
  • Inserting an "Input message" tab using Data Validation
  • Inserting an "Error alert" tab using Data Validation
1. Create a separate list of data items which we need for the drop-down
2. Select a cell where the drop down list is required
Go to:  “DATA” tab in ribbon >> Data Validation button >>> Data Validation [Shortcut: Alt+AVV]

3. In the Data Validation dialog box, go to settings and select "List" from the drop down menu

Ensure that "In-cell dropdown"  button is checked. Also, check "Ignore blank" if you want to allow blank entries.
4. You can manually enter the range which you require in drop-down list OR can give cell reference in the "Source" field

5. Press "OK"
Inserting an "Input message" tab using Data Validation
The input message provides the information regarding the valid input data to the user when the cell containing the drop-down is selected.
  • In the Data Validation dialog box, go to "Input Message" tab
  • Enter the required title and input message that you want to populate (ensure that "Show input message when cell is selected" is checked)
Inserting an "Error alert" tab using Data Validation
Error alert populates when any invalid data is entered into the cell by the user.
  • In the Data Validation dialog box, go to "Error Alert" tab
  • Select the error symbol from "Style" drop-down
  • Enter the required title and error message that you want to populate when error occurs (when the data entered in the input cell is different from the validation list)