ExcelHub - Learn Excel Easily

Row 1 Is Missing. How to unhide?

2017-04-17T11:22:00.001+05:30

Excel Tip #030

Row 1 Is Missing. How to unhide?

As you know, hiding and unhiding rows in Excel is simple as selecting them, right-clicking and choosing Hide or Unhide. The trick to selecting hidden rows, is to select a row above or below and then drag to included the hidden rows.

For example, if you accidentally hide row 1 of your worksheet or inherit someone else's worksheet with one or more of the top rows hidden, you may be wondering how to unhide them since apparently you cannot select them.

Row 1 Hidden

There is a really easy solution.

1) Point to the row 2 header, press the left mouse button and drag upward to the top of the worksheet. Although you cannot see it, row 2 and row 1 are now selected
2) Right click anywhere on row 2
3) Choose Unhide from the right-click menu

Unhiding hidden Row 1

NOTE: This also works for multiple hidden rows.

There is another method of doing the same if Row 1 is hidden.
1) Go to the top left corner of the sheet above Row 2
2) You will see the mouse changes to an extending black arrow
3) Left click and drag it down, you can see Row 1 now
4) If there are multiple rows hidden, you can follow steps 1 to 3 but only one row will be hidden at once. To avoid this, use the first method of unhiding mentioned before.

Drag hidden row 1 from top to bottom

What if that doesn't work?
You may come across a situation where the top several rows of a worksheet appear to be hidden but the solution above doesn't unhide them. In that case, the problem isn't that the rows are hidden. It is more likely that one of the Freeze Panes options has been enabled.

Solution: From the View tab click Freeze Panes. If the Unfreeze Panes option is available, click it to make your rows visible again.

1) Open a new workbook
2) Scroll down so that the first few rows are no longer visible at the top of the worksheet
3) From the View tab click, Freeze Panes, Freeze Top Row
4) Now scroll back up and you'll see that the top rows appear to be hidden
5) To make them visible again, from the View tab click Freeze Panes, Unfreeze Panes

Subtotal Dates By Month (or) Year

2017-04-12T11:10:00.002+05:30

Excel Tip #029

Subtotal Dates By Month (or) Year

There may be times when you want to subtotal your data by month and year, however simply Subtotaling a column of dates won't work because that will create a subtotal for each day.

Subtotal Dates - Example

Here's a trick you can use to create subtotals for each month while ignoring the day...

1) Sorted your dates by selecting a single date within the table and from the Data tab click the sort A>Z button;

2) Next you need to apply a date/number format that displays the month but not the day (e.g. mmm yyyy). To do this, from the Number group on the Home tab, click the Number Format dropdown (or press CTRL+1) and choose More Number Formats..., then click Custom and enter mmm yyyy in the Type box. All of the dates now display the month and year (e.g. Apr 2015)

Formatting Dates

3) To subtotal your data based on the month and year in the Date column, from the Data tab, click Subtotal,. in the Subtotal dialog, 'At each change in' select your date column heading (i.e. Date), for 'Use function' select Sum, for 'Add subtotal to' use Amount. Subtotals will be inserted for each month/year instead of each day

Subtotal Dates

4) You can now format your dates back to the original date format to display the full dates (day, month and year) and the subtotaled rows will still show just the month and year

Dates in Original Format

Use of Function keys in Excel

2017-04-12T11:03:00.000+05:30

Excel Tip #028

Use of Function keys in Excel

Most of us know few function keys that are used as shortcuts in Excel, but hardly make use of it. Either because of the keyboard settings or due to the scarcity of the knowledge.

Below, the use of function keys are tabulated along with their combinations which will make your life much more easier.

Function Key = F1 to F8

Function Key = F9 to F12

Try to use these Function key shortcuts in your day to day tasks and be benefited.

Find And Replace Cell Colors

2017-04-10T11:22:00.001+05:30

Excel Tip #027

Find And Replace Cell Colors

It is common for Excel users to apply background colors to cells, such as green to indicate for positive, red to indicate warning etc. But how to remove the green fill color from hundreds of cells throughout the workbook without having to do them one by one.

Steps to Find cells with a specific Fill color

1) Press CTRL+F to open the Find dialog
2) If you don't see the Format button, click the Options>> button
3) Click the Format... button
4) Click each of the tabs (Number, Alignment, Font, Border, Fill and Protection) and, if the Clear button isn't 'grayed out', click it to clear any formatting from the search criteria
5) Click the Fill tab and choose the color you wish to find

6) Click the Find All button. All of the cells with the Fill color you selected will be listed at the bottom of the Find and Replace dialog

To Replace the Fill color in the 'found' cells

  1) In the Find and Replace dialog, click the Replace tab
  2) Click the Format... button in the 'Replace With' section
  3) Click the Fill tab and select the color you wish to replace the 'found' cells with

  4) Click the Replace All button.

Move Columns & Rows Quickly

2017-04-10T11:06:00.001+05:30

Excel Tip #026

Move Columns & Rows Quickly

I often see Excel users 'moving' a column by inserting a blank column where they want to move the column, drag the original column and drop it in the empty column, the go back and delete the empty original column. Same thing for rows.

There is a much, much easier and faster way!

Move Columns
To move a column (or multiple columns) and insert it (them) between two other columns,
1) Select the column(s) you want to move
2) Hold down the SHIFT key. Holding down SHIFT tells Excel to 'insert' the columns
3) Point to the edge of the selected column(s)
4) Press and hold your left mouse button to drag and position the 'insertion bar' where you want to move the column(s)

5) Release the mouse button, then release the SHIFT key

Move Columns

Alternatively, Right-click column heading you want to move, select Cut, right-click the column heading to the right of where you want to move it, select Insert Cut Columns.

Move Rows
To move a row (or multiple rows) and insert it (them) between two other rows.

1) Select the row(s) you want to move . Holding down SHIFT tells Excel to 'insert' the rows
2) Hold down the SHIFT key
3) Point to the edge of the selected row(s)
4) Press and hold your left mouse button to drag and position the 'insertion bar' where you want to move the row(s)
5) Release the mouse button, then release the SHIFT key.

Move Rows

Alternatively, Right-click row headings you want to move, select Cut, right-click the row heading immediately below where you want to move it, select Insert Cut Rows.

Move block of cells
You can use a similar technique to move just a selected block of cells.

1) Select the cells you want to move
2) Hold down the SHIFT key. Holding down SHIFT tells Excel to 'insert' the cells
3) Point to the edge of the selected cells
4) Press and hold your left mouse button to drag and position the 'insertion bar' where you want to move the cells. When you drag horizontally, the insertion bar will be positioned between two columns. When you drag vertically, the insertion bar will be positioned between two rows

5) Release the mouse button, then release the SHIFT key.

Move Block of Cells

Right-click the cells you want to move, select Cut, right-click the cell immediately below or immediately to the right of where you want to move them, select 'Insert Cut Cells ' and choose 'Shift cells right' or 'Shift cells down'.

Return the last value in a list

2017-04-07T12:06:00.001+05:30

Excel Tip #025

Return the last value in a list

You would have faced some situations where you want to get the last value in the list. And this list will not be static, the list will grow further and still the the last value has to be returned. Quite interesting...!

There might be multiple ways but as far I know, there are 2 ways in which we can get this done. But as far I consider, there are loopholes in all the 2 methods. Let us get in detail with all the 2 methods.

Method 1

This method uses the function LOOKUP. Let us see get to know about this function.

LOOKUP function returns a value from a range (one row or one column) or from an array. The syntax is:

LOOKUP( lookup_value, lookup_range, [result_range] )

(or)

LOOKUP( lookup_value, lookup_range )

We are going to use a hidden excel function in this method which is 9e9. So what does this 9e9 give? 9e9 is just a notation for 9,000,000,000 which is considered as the largest value in the array. It is nothing like an exponential function, 9 followed by 9 zeroes in short.

The formula is,

=LOOKUP(9e9,A:A)

Once you type this formula, 9e9 gets converted to 9,000,000,000. The formula will return the last value as the list grows.

Method 1 - LOOKUP & 9e9

Loophole - Method 1:

1) It is used only for values and doesn't consider character in the list

2) The values need not be continuous. If you enter a number in cell A20 leaving cells A11 to A19 as blank, it will return the value entered in A20. This is a loophole because, if accidentally entered a number at the end, and then sending the report as it is will screw up everything.

Method 2

This method uses the functions INDEX & COUNT/COUNTA.

INDEX - Returns a value in a table based on the intersection of a row and column position within that table

COUNT - Counts the numbers of cells in a range that contain numbers

COUNTA - Counts the numbers of cells in a range that are not empty (doesn't consider whether it is number or character)

The formula is,

=INDEX(A:A,COUNTA(A:A))

Method 2 - INDEX & COUNT

One main advantage in this method is if you use COUNTA function, then we can make use for character (or) for strings too.

Loophole - Method 2:

1) If you are using COUNT and if there are any blank values in between, the result will not be proper. The list should be continuous and the values should start from cell 1.

So which of these methods will you be using?

Calculate Weighted Average

2017-04-06T14:39:00.000+05:30

Excel Tip #024

Calculate Weighted Average

Excel makes it extremely easy to calculate the average of data by using the AVERAGE function. But what if some of the values have more "weight" than others? For those cases, you'll need to calculate the Weighted Average.

Although Excel doesn't have a weighted average function, it does have a function that does most of the work for you: SUMPRODUCT.

In order to calculate the weighted average, we are having 2 columns of data. The first column contains the grades for each assignment and the second column contains the weights. A higher weight will cause the data (assignment) to have a greater effect on the final grade. The weights can also be greater than 100% but it would be meaningful if it is 100%

Below is the example data containing 5 assignments along with their marks and the weights.

Example - Weighted Average

Before going to the example, let us learn about SUMPRODUCT. This function multiplies the corresponding items in the arrays and returns the sum of the results.

Syntax:
SUMPRODUCT(array1, [array2], [... array n])
where array1 is mandatory and the rest of the array are optional.

We are going to use SUM function along with SUMPRODUCT to calculate the weighted average. In our example,
B2:B6 - Range of cells that contains the marks
C2:C6 - Range of cells that contains the weights

The formula is,
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

We are defining the marks as Array1 and their corresponding weights as Array2. Finally the resultant is divided with the sum of the weights to get the Weighted Average.

Calculating Weighted Average

SUMPRODUCT is multiplying (finding the product of) each assignment's marks times its weight, then adding all of the products together. In other words, it finds the sum of the products, which is where it gets its name. So for Assignment 1 it multiplies 85 by 20, and for Assignment 1 it multiplies 92 by 15 etc.

The way the SUMPRODUCT function calculates is

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)

By itself, SUMPRODUCT will give us a huge number 8,925. This is where the second part of our formula comes in: /SUM(C2:C6). This part brings the value back down to a normal grade range, making the answer 89.25.

Remember that the weights don't need to add up to 100%, SUM part will take care of that.

Prevent Automatic Hyperlinks in Excel

2017-04-05T10:45:00.002+05:30

Excel Tip #023

Prevent Automatic Hyperlinks

Having Hyperlinks in your worksheet that allow you to quickly and easily open other related workbooks, can save you a lot of time. You don't have to browse to the folder where the workbook is located.

Many of us use hyperlinks to jump from one sheet of the workbook to another sheet (or) to another workbook. But it can be annoying sometimes when you simply want to enter the data (e.g. website (or) email address) in your worksheet and it automatically gets created as hyperlinks.

By clicking the Undo command (on the Quick Access Toolbar) or pressing CTRL+Z immediately after you create the hyperlink, you can quickly disable that hyperlink but that is not going to be a permanent solution.

If you just don't want hyperlinks created in your workbooks every time you type a website (or) email address, you can disable this feature in Excel's options.

From the File tab, click Options, Proofing, then click the AutoCorrect Options... button
On the 'AutoFormat As You Type' tab, remove the checkmark from the 'Internet and network paths with hyperlinks' option and click OK.

Preventing Automatic Hyperlinks - Website (or) Email address

PS: Note that this option will disable the automatic creation of hyperlinks for all workbooks, so whenever you type an email address or URL in your workbook, Excel won't automatically turn it into a hyperlink.

If you do need to create an occasional hyperlink, from the Insert tab, click Hyperlink or press CTRL+K

Search Backwards in Excel

2017-04-05T10:24:00.000+05:30

Excel Tip #022

Search Backwards in Excel

Sometimes we take some things for granted in Excel and assume that everyone else knows too. Here's a tip which lot of Excel users don't know about it.

Many Excel users know that the quickest way to find something in a large worksheet is to use Excel's Find command. On the Home tab, in the Editing group, click Find & Select, then Find or, preferrably, use the keyboard shortcut CTRL+F instead. Each time you click Find Next you will be taken to the next matching cell, starting from the active cell and going down the sheet until you finally come to the last one.

But what if you want to find the 'last' matching value in a sheet and there are dozens or even hundreds of matching values?

You don't want to click through each one until you finally reach the end. Here's a simple trick that will save you some time.

Select a cell below the range you want to search (CTRL+END is a shortcut you can use to quickly get to the bottom of your worksheet)
Press CTRL+F to open the Excel's Find & Replace dialog, then enter the value you are searching for
Now hold down the SHIFT key and click Find Next. If there is a matching value in the worksheet, you will be taken to the last one on the sheet
Subsequently, each time you click the Find Next button while holding down the Shift key, you will be searching backwards from the bottom to the top of your worksheet, finding each matching value until it finally reaches the first matching value

Pressing CTRL+F, then SHIFT+FIND NEXT starts searching at the 'active cell' and goes upward. When it reaches row 1, it jumps back to the bottom of the worksheet and continues searching upward until it gets back to the original active cell or the first matching value if there is one. So, you start from row 1, it will immediately jump to the bottom.

Quickly Identify Column Differences

2017-04-04T11:51:00.001+05:30

Excel Tip #021

Quickly Identify Column Differences

If you need to compare two or more columns of data, there are various methods you can use in Excel to check for differences. Sorting, Conditional Formatting and using formulas such as MATCH or VLOOKUP are just a few of them. For years, whenever I needed to compare two columns of data, I used a simple formula such as =B2=C2 that would give me TRUE if the cells are the same and FALSE if they aren't.

But there's and even easier and faster way identify differences in columns of data without using formulas.

Select the columns to compare
Press press CTRL + \ (or press F5, Special.., Row Differences, OK). Cells in the 'non-control' column(s), whose values don't match the values in the control column, are selected
Add a background Fill color to make the selected cells more visible
For 2-column and multiple row comparisons, Right-click one of the highlighted cells and choose Filter, Filter by Selected Cell's Color.

Consider the below example, 10 sales rep and their corresponding points awarded in the last 3 months. We want to compare whether the sales rep have got any points from their base points (from last year) in the last 3 months (or) not.

Column Difference - Comparison

Keyboard shortcuts for formatting

2017-04-04T11:31:00.000+05:30

Excel Tip #020

Keyboard shortcuts for formatting

A lot of us use keyboard shortcuts but when it comes to formatting a spreadsheet, these shortcuts go for a toss as formatting relies on mouse.

But there are few simple & effective shortcuts which would help us a lot. Below are some...

CTRL+1 – Format anything
This shortcut is powerful. Select anything (cells, chart objects, drawing shapes, pictures etc.) and press CTRL+1 to instantly launch format dialog box.

ALT EST (or ALT HVE) – Format painter
If you want to copy the formatting from one thing to another (like formatting of a bunch of cells to another range, a chart to another chart), you can use Format painter. Simply copy the original object (CTRL+C), select the target object and press ALT+EST (one key after another). You may also use ALT+HVE (again one key after another) in all modern versions of Excel.

ALT HH – Fill color
Now comes the tricky one. If you want to fill some color in a chart object, drawing shape, cell or something else, you can select it, press ALT HH. This activates the fill color box. Here, you can use arrow keys to select the color you want and press enter to fill it.

ALT HFC – Font color
This is same as above. FC is a short for Font Color. Press they keys one after another and the font color box opens up. Just use arrow keys to pick the color you want and press enter.

F4 – Repeat last action
Let’s say you want to fill yellow color in a bunch of cells. But these cells are not together. So you to the first one, press ALT HH and fill yellow color. Now, you go to the second cell. Will you press ALT HH again? No silly, you just press F4. This fills yellow color in the second cell (and all other subsequent cells) for you.

The F4 key works great when formatting charts too. You can format one series (or chart element the way you want), select other items and press F4.

Alt key, for everything else
Anything that is on ribbon in Excel can be accessed with ALT key and a sequence of letters/numbers. There is no point memorizing such sequences. Instead, you can look at ribbon while pressing the ALT key and learn the shortcuts on the go.

Quickly locate active workbook's folder

2017-04-03T14:42:00.000+05:30

Excel Tip #019

Quickly locate active workbook's folder

We will be working on (or) will be having multiple workbooks open at a time. Sometimes I want to know the folder location for the file that I am working on. And sometimes I need to go to that folder to access other related files.

A cool trick that I find handy involves adding the Document Location command to my Quick Access Toolbar (QAT).

Add the Document Location command to your QAT

Right click anywhere on the Ribbon and select "Customize Quick Access Toolbar"
On the "Choose commands from" dropdown, select All Commands
In the list of commands (on the left side of the dialog), scroll down and select the Document Location command
Click the Add>> button and click OK

Document Location command in QAT

Note: You can see the document location (or) the folder path only if the workbook is saved

The "Document Location" command is not only useful to show the full path of the active workbook in your QAT, but if you

Click in the Document Location field
Select and delete just the filename (keep just the path) and
Press Enter, a new Explorer window will open up with the current workbook's folder selected

Try out this useful tip...!

Print your Worksheet pages in the order you want

2017-04-03T13:55:00.002+05:30

Excel Tip #018

Print your Worksheet pages in the order you want

Did you know that you can print your worksheet pages in any order you want? Yes, instead of printing a worksheet as pages 1-2-3-4, you can print pages 4-1-3-2, or any other order you want, without having to reorganize your worksheet.

You may have a simple solution, why can't we just change the page order and then print. That's a good answer but what if the worksheet is a client report and it is only in read only format.

All you have to do is to hold down the CTRL key and select each area you want to print for each page, in the order you want them to print.

In this example, let's say you need to print pages 4-1-3-2 in that order.

Before you begin, you may want to save your workbook if you don't plan on keeping this new print order. That way, if you make any changes to the current print area, you can close and re-open your workbook without saving those changes.

You may find it easier to do this in Page Break Preview mode. So, if you want, click the small Page Break Preview icon on the bottom right of the Status Bar, just to the left of the Zoom % (or) from the View tab, click the Page Break Preview command in the Workbook Views group. In earlier versions of Excel, you'll find the Page Break Preview command on the View menu.

Page Break Preview Option

Select pages in the order to be printed (e.g. 4,1,3,2)

First, drag to select the range (page 4) that you want to print first
Next, hold down the CTRL key and drag to select the range (page 1) you want to print next
With the CTRL key still down, drag to select page 3
With the CTRL key still down, drag to select page 2
At this point you can either...

Print the selected ranges

Print the selected ranges
Reset the Print area (Remember to save your workbook before changing the Print Area if you aren't planning on keeping the new print area)

Reset the Print area

This tip can be very useful when you sometimes want to print an adhoc report, maybe just small sections of your current worksheet.

Limit # of characters in a cell

2017-04-03T09:38:00.001+05:30

Excel Tip #017

Limit # of characters in a cell

We would have come across situations where we want to restrict the end user from typing lengthy sentence in a cell which would then get itself autofit (or) sometimes we don't see the entire comment if the adjacent cell has data in it.

To overcome this situation, we can limit the end user to have only certain number of characters that can be typed in a cell.

In our example, the sales rep address should not be greater than 20. So we are limiting the range of cells to be greater than (or) equal to 20. For ease, I have created a column name length which displays the length of the address for each sales rep.

Example - Limit # of characters in a cell

You can see that the address of the sales rep Walker (in cell B10) is greater than 20. So we are going to give a constraint.

Here's how you can limit the number of (text) characters that can be entered in a range of cells:

Select the range of cells you want to limit to a maximum number of characters
From the Data tab click Data Validation in the Data Tools group
In the Data Validation dialog box, select the Text length from the "Allow:" dropdown
From the Data dropdown select "less than or equal to"
In the Maximum field type the maximum number of characters allowed (or use a reference to a cell containing a number)
Click OK

Limit # of characters in a cell - Data validation

Now we have defined a constraint. But now this will not show an alert (or) error since the # of characters in cell B10 is greater than 20. For that, edit cell B10 and then press Enter, an error message will pop up. This will restrict the end user to use only 20 characters in a cell.

Limit # of characters in a cell - Data validation Created

Create Your Own Custom Data Validation Messages

Input Message

If you want, you can customize the messages the user sees. Data Validation allows you to create your own custom Input Message and Error Alert message.

By default, no message displays when a user merely selects a cell within a Data Validation range. However, when you apply Data Validation to a range of cells, it is often good practice to display a message to alert the user to the data validation requirements.

In the Data Validation dialog box, select the Input Message tab
Place a checkmark in the 'Show input message when cell is selected' option
Type a message and message title that you want to display when a user clicks a cell within the Data Validation range
Click OK

Error Alert message

The default Error Alert message tells the userthat they've entered an invalid value in the range but it doesn't tell them why. Creating your own custom Error Alert allows you to explain to the user why the value they input was invalid.

In the Data Validation dialog box, select the Error Alert tab
Place a checkmark in the 'Show error alert after invalid data is entered' option
From the Style dropdown, choose a icon to be displayed in the Error Alert message dialog box
Enter a Title and Error message that you want to display if a user enters an invalid value
Click OK

COUNTIF Partial Matching

2017-03-30T10:00:00.002+05:30

Excel Tip #016

COUNTIF Partial Matching

There are many COUNT functions that are available in Excel. One of which is the COUNTIF() function. Let us see a more detailed use of it.

The use of COUNTIF() function - It is used to count the number of cells in a given range which meets the specified condition. Its syntax is,

=COUNTIF(range,criteria)

The range field is the group of cells that you want to count. The criteria field tells the cells that needs to be counted which can be a number, expression, cell reference, or text string.

Consider the below example.

Example - COUNTIF() function

=COUNTIF(A2:A6,"Cadbury") - This gives me 1 as the result
=COUNTIF(A2:A6.A5) - In A5 we have Toblerone and the function searches for Toblerone in the list which is 1
=COUNTIF(B2:B6,23) - Here Mars chocolate has 23 and the result will be 1
=COUNTIF(B2:B6.">50") - The condition is to count cells which are above 50. We have Toblerone and Ferrero Rocher have quantity 61 & 87. Hence the result will be 2
=COUNTIF(A2:A6,"*") - Counts the number of cells containing any text in cells A2 through A6. The asterisk (*) is used as the wildcard character to match any character. The result is 5

Now since we have seen the use of asterisk (*), the wildcard character, let us now see how this can be used for Partial Matching.

Example - COUNTIF() Partial Matching

You may find that there are many situations where you need to count the number of cells in a range that only partially match your criterion.

By adding asterisks to the beginning and end of your criteria, for example, =COUNTIF(A2:A15,"*Cadbury*") will count all cells that include the word 'Cadbury' anywhere within the cell.

For the formula, =COUNTIF(A2:A15,"*Cadbury*") the result will be 6 since there are 6 rows of data with the word Cadbury irrespective of what the data is before and after the word.

We can use this partial matching technique to find the words which are with spaces too. For example let us take the word Kat only in "Kit Kat"

=COUNTIF(A2:A15,"*Kat*"), will give me result 3.

Hope this is a useful tip and can be used effectively for counting the cells being partially matched.

Alphabets auto-fill

2017-03-28T09:59:00.000+05:30

Excel Tip #015

Alphabets auto-fill

You would have done auto-fill for numbers in Excel but auto-fill for alphabets is not possible. This is because Excel is a number crunching tool. But first how do you auto-fill numbers in Excel.

Type 1 in A1, 2 in A2 and 3 in A3
Then click the fill handle (which is the thick block at the right bottom corner)
Drag the fill handle to the number of rows you want the data to populate

Auto-fill Numbers

To do the same, there is a hidden Excel shortcut. To do this, you don't need to enter 3 numbers, just 1 in A1 would do.

Type 1 in A1
Select cells from A1 to A10 to populate numbers from 1 to 10
Type ALT + E + I + S
A pop-up window will open, check the Trend box
Click OK

Auto-Fill Numbers Hidden Excel Shortcut

But this is not possible in the case of Alphabets. But we have an option which can enable this feature.

Click File menu
Go to Options tab
Click on Advanced options
Scroll to the bottom in the pop-up window that appears. You will find an option General
Click on Edit Custom Lists option
Here you can create a new list. By default NEW LIST is enabled. Enter the alphabets in the List entries space given
Upon entering the alphabets, click on ADD. The new list will be available
Now you can use the auto-fill option for alphabets

Enabling Auto-fill option. Custom lists

Creating Auto-fill Custom Lists

Dynamic Name Range Charts in Excel

2017-03-23T11:30:00.001+05:30

Excel Tip #014

Dynamic Name Range Charts in Excel

It would be nice if a chart that gets updated right before your eyes at each data entry. This can be done creating Dynamic Charts. The key is to define the chart's source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data.

There are two ways to build dynamic charts in Excel - Table method and Formula method. Let us see how to work with both the methods.

Consider the below example having the sales for 5 sales rep.

Example - Dynamic Chart

Table Method:

The first step is to create the table. To do so, simply select the data range and do the following:

Click the Insert tab.
Click Table.
Excel will display the selected range, which you can change. If the table does not have headers, be sure to un-check the "My table has headers" option.
Click OK and Excel will format the data range as a table.

Creating the data as Table

Now we have created a Table which acts as a dynamic range. Any chart you build on the table will be dynamic. To illustrate, create a quick column chart as follows:

Select the table.
Click the Insert tab.
In the Charts group, click Column and choose the first 2-D column chart.

Dynamic Chart - Table Method

Now in the consecutive rows, I have added 2 sales rep (Leslie and Luther) and their sales and the chart adds those data and changes dynamically. Please refer the below image.

Data added in Table - Chart changes dynamically

Formula Method:

You won't always want to turn your data range into a table. Furthermore, this feature isn't available in pre-ribbon versions of Office. When either is the case, there's a more complex formula method. It relies on dynamic ranges that update automatically, similar to the way the table does.

To do this, we must learn to use 2 formulas - OFFSET() and COUNTA().

OFFSET():

The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. Its syntax is,

=OFFSET(reference, rows, cols, [height], [width])

Arguments

reference - The starting point, supplied as a cell reference or range.
rows - The number of rows to offset below the starting reference.
cols - The number of columns to offset to the right of the starting reference.
height - [optional] The height in rows of the returned reference.
width - [optional] The width in columns of the returned reference.

Consider the same example, sales of 5 sales rep. Using offset(),

=OFFSET(A2, 3, 1, 1, 1)

OFFSET() Example

In the above example, it returned the value 655 (sales value of Deborah) from 3rd row and 2nd column with reference to cell A2.

COUNTA():

TThe COUNTA function counts the number of cells that are not empty in a range. Its syntax is,

=COUNTA(value1, [value2], ...)

Arguments

value1 - An item, cell reference, or range.
value2 - [optional] An item, cell reference, or range.

Consider the below example, using COUNTA() in it,

COUNTA() Example

To create dynamic chart, we have to first create a dynamic named range. Follow the below steps,

Click the Formulas tab.
Click the Define Names option in the Defined Names group.
Enter a name for the dynamic range, SalesRepData.
Enter the following formula =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)
Click OK.

Defining Name Range

I am referencing A2, with rows and columns set to 0. Then using COUNTA function to count the number of non-blank cells.

You will definitely have a question, why I used -1 at the end. This is because, I am referencing from A2 and COUNTA(A:A) will give me 6 (including the headers). I want to consider only the sales rep information and not the header. Hence I have given -1.

Similarly create another dynamic range SalesData for sales information

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

Now create a simple 2-D column chart using the SalesRep and Sales data.
Click on any of the bar inside the chart and click Select Data.
A small pop-up window opens displaying the data range of the chart.
Click on Sales under Legend Entries and then select Edit.
In the Series values from =Sheet1!$B$2:$B$6 change it to =Sheet1!SalesData
Likewise, click on Horizontal axis label and select Edit. Change the Axis Label Range from =Sheet1!$A$2:$A$6 change it to =Sheet1!SalesRepData
Now dynamic named ranges are added in the chart, making it a dynamic chart

Dynamic Chart - Formula Method

If you notice, there is a difference when adding data while using Table method and Formula method. In table method, the chart will get updated at each data entry point. But in Formula method, the chart gets updated only when the entire row of data is completed.

VLOOKUP - Exact Match & Approximate Match

2017-03-22T15:57:00.001+05:30

Excel Tip #013

VLOOKUP - Exact Match & Approximate Match

In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful.

Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match. Let us first refresh the syntax of VLOOKUP.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In simple terms,

= VLOOKUP(value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match)

Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact).

If range_lookup is set to FALSE, the formula searches for exact match, i.e. for the lookup value exactly as you've entered it in the first parameter (lookup_value )
If there are two or more values in the first column of table_array that match the lookup value, the 1st value found is returned. If an exact match is not found, the #N/A error is returned
For example, if you use the formula =VLOOKUP(4, A2:B15,2,FALSE), but your data does not contain value 4 in cells A2 through A15, the formula will return #N/A
If range_lookup is set to TRUE or omitted, the formula searches for approximate match. More precisely, your VLOOKUP formula will search for an exact match first and if an exact match is not found, it will return an approximate match. An approximate match is the next largest value that is less than lookup_value.

Note: If you enter TRUE or omit the range_lookup argument, the values in the first column of the lookup range must be sorted in ascending order, i.e. from smallest to largest. Otherwise, the Excel VLOOKUP function may not find the correct value.

VLOOKUP with approximate match:

Consider the below example, Rank and Sales Rep.

Example - VLOOKUP, Approximate match

The first thing you need to do is sort the first column in your lookup range in ascending order. This is very important because your VLOOKUP formula is going to return the next largest value for the lookup value you specify and then stop searching.

If you neglect to sort your data properly, you will end up having really strange results or #N/A error.

Sort data to use Approximate match in VLOOKUP

Below is an example to see the closest rank to rank 10.

Use of Approximate match, rank closes to rank 10

You can see rank 11 is available but the resultant shows rank 7. This is because the VLOOKUP function with approximate match returns the next largest value that is less than the lookup value.

Some more examples:

Some more examples - VLOOKUP with Approximate Match

Did you the second question, to find the Sales rep who ranked 13? As highlighted in the image, there are 2 Sales Rep who are ranked 13, Bessie and Horace but the final result shows Horace. Why?

This is because when we use Approximate match, VLOOKUP will look for all matches of ranked 13 and will fill in the later part by overwriting if it has found already.

If using Exact match for rank 13, you will see Bessie as the result. This is because Exact match will display the first found solution.

This is a difference when using Exact Match & Approximate Match in VLOOKUP.

VLOOKUP & INDEX + MATCH

2017-03-22T15:08:00.003+05:30

Excel Tip #012

VLOOKUP & INDEX + MATCH

Excel has many formulas which functions differently. To analyze data, one of the most important formula is VLOOKUP and INDEX + MATCH. Let us now get into the details of it.

VLOOKUP:

Let us consider the below data as an example.

Sales Data

Let us say, we want to know how much sales did Jerald make? VLOOKUP can be used to answer this question.

What does VLOOKUP actually do?

VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In simple terms,

= VLOOKUP(value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match)

The final parameter is optional but very important. In the next tutorial, I will provide a few examples explaining how to correctly make formulas for exact (or) approximate match for VLOOKUP.

In our example of finding the sales of Jerald, we need VLOOKUP to search for Jerald (cell A6) and return the amount of sales he made from column 3.

The formula will be:

=VLOOKUP(A6,$A$2:$C$11,3,FALSE)

VLOOKUP to get sales data for Jerald

To fetch the units sold by a person, the column # should be 2.

Some more examples:

Few more examples on VLOOKUP

You can notice that the 3rd question is left unanswered. This is because of a drawback of VLOOKUP, it cannot lookup values in the left.

To overcome this, we have to make use of the combination INDEX & MATCH in the next part.

INDEX + MATCH:

VLOOKUP can only return corresponding items from the column at right, not at left. One easy fix would be move the sales data to the left of sales rep. But this is an annoying and later you may want to lookup based on unit values too. So let us see can INDEX & MATCH can handle this scenario.

Below are the syntax of INDEX & MATCH.

=INDEX(reference, row_num, [column_num])

=MATCH(lookup_value, lookup_array, [match_type])

The basic syntax of this combination is like this

=INDEX(column with data you want,MATCH(value you are looking for, column which contains this data,0)

Our problem is to find out the sales rep who made sales = $470. Sales rep names are in column A and the value $470 is in cell C8. The formula will be,

=INDEX($A$2:$A$11,MATCH(C8,$C$2:$C$11,0))

INDEX + MATCH to get the sales rep name who made sales $470

Some more examples:

INDEX + MATCH can also work like VLOOKUP, meaning INDEX + MATCH combination is not only used to lookup values in the left but also towards right. Look at question 3 in the below example

Few more examples on INDEX + MATCH

Zoom to make your spreadsheet fit your screen

2017-03-22T10:42:00.001+05:30

Excel Tip #011

Zoom to make your spreadsheet fit your screen

Usually a new Excel sheet is available at Zoom % of 100. Sometimes for the look and feel of the workbook, we usually change the zoom %, I usually have my workbooks mostly at 90% zoom.

But to fit the data into the screen, we might have to decrease the zoom % further so that the entire data is viewed properly. Before going to our today's tip, let us first check out the ways we can zoom in (or) zoom out the Excel sheet.

Option 1 (Usual method):

People mostly follow either of these 2 usual methods. One way is going round the Excel ribbon tab and the other is using the Zoom option available at the right bottom corner of the sheet.

Using Zoom option via Excel ribbon

Using Zoom option at the right bottom corner of the Excel sheet

Option 2 (Keyboard Shortcut):

When we move over the Excel ribbon by pressing ALT keys, alphabets appear. The shortcut key is ALT + W + Q

Shortcut key using Excel Ribbon

A pop up window as shown in the below image will appear, where you can resize the Zoom %

Pop up window for resizing the Zoom %

Option 3 (Mouse Shortcut):

Press the CTRL key and move up (or) down the scroll wheel in the mouse, Moving up will increase the Zoom % and moving down will decrease the Zoom %. By default this increase (or) decrease will move by 15% (i,e.) 85%, 100%, 115% etc

Fitting your screen:

Consider the below sales data of 15 products in 2016 for all the months. Here, Excel default Zoom % of 100 is set. If you see, the entire data is not fitted into the screen.

When the data is slightly wider than will display on the screen I've seen people try using smaller font sizes, make columns narrower, or abbreviate data in some columns just to make it fit. There is a much easier way.

Data not fitting the screen

Below is the method to fit the range of cells to the screen size.

Select a range of cells from the left-most cell (usually column A) to the right-most cell in your sheet. It doesn't matter how many rows, one or two will do
Using option 1 & option 2 as mentioned above, you will see a pop up window. Click Zoom to Selection. Excel will automatically adjust the zoom percentage so that the cells you have selected will fit into the visible area of your screen

Fit Selection option in Zoom pop up window

Data Fitted in the screen

We can also achieve this in another method where we have an option Zoom to Selection in the View ribbon tab which does the same.

Zoom to Selection option in View tab

Shortcuts for the sheet to fit the screen:

ALT + W + Q + F + Enter
ALT + W + G

Changing default chart color layout

2017-03-21T22:24:00.001+05:30

Excel Tip #010

Changing default chart color layout

Whenever we want to use the chart series, Excel comes up with certain default colors to beautify the charts. It is high time we should know to change the default color layout and below is the same.

To change the colors of the current workbook:

On the Page Layout tab, in the Themes group, click Theme Colors

Theme Colors under Page Layout tab
Click Customize Colors

Customize Colors option
In the Create New Theme Colors dialog box, under Theme colors, click the button of the theme color element that you want to change
Under Theme Colors, select the colors that you want to use

Choosing colors for the new theme
In the Create New Theme Colors dialog box, under Sample, you can see the effect of the changes that you make
Repeat step 3 and 4 for all of the theme color elements that you want to change
In the Name box, type an appropriate name for the new theme colors
Click Save

To revert all theme color elements to their original theme colors, you can click Reset before saving

To change the colors in all workbooks:

If you would like to make these color changes apply to all new workbooks that you create, you need to create a default workbook template.

On the Page Layout tab, in the Themes group, click Themes
Click Save Current Theme

Saving the current theme
In the File Name box, type an appropriate name for the theme
On the File tab, choose Save As
In the Save Current Theme dialog box, was selected Office Theme (*.thmx) and choose the Document Themes folder as the location. You can use your own filename instead of proposed Theme1.thmx
Click Save

The location of the Document Themes folder may vary, but it is usually located here:

C:\Users\***\AppData\Roaming\Microsoft\Templates\Document Themes

Now you have the prepared Theme1 as the default color layout in all your workbooks.

Inserting and deleting cells, rows and columns

2017-03-21T17:07:00.001+05:30

Excel Tip #009

Inserting and deleting cells, rows and columns

There are ways inserting and deleting cells, rows and columns. Below are 3 methods (which I can recollect) in inserting (or) deleting rows and columns.

Option 1 (Standard Procedure):

Selecting a row (or) column by clicking it, then right click for options. You can see options "Insert" and "Delete". This will add a new row in the left (or) a new column in the top.

Inserting/Deleting row (or) column - Option 1 (Standard Process)

Option 2 (Unusual mouse method):

You can quickly insert a range of empty cells or rows by holding down the SHIFT key while dragging the fill handle.

To insert a range of cells, select a single cell above where you want the cells inserted and drag the fill handle down the number of cells you want to insert.

Inserting/Deleting row (or) column - Option 2 (Unusual mouse method)

Inserting columns works basically the same way by dragging the Fill Handle horizontally to the right.

You can also delete cells, rows and columns using this same basic technique.

1) Select the cells, rows or columns you want to delete

2) Drag the fill handle back over the selected cells

Option 3 (Shortcuts):

When you press the ALT key, there will be alphabets displayed. Press "H" for Home, "I" for Insert, "R" for Rows.

Shortcuts using ALT key:

Insert Sheet Rows: ALT + H + I + R

Insert Sheet Columns: ALT + H + I + C

Delete Sheet Rows: ALT + H + D + R

Delete Sheet Columns: ALT + H + D + C

Adding/Deleting rows (or) columns using ALT key shortcut

To select entire row (or) column, press SHIFT + SPACEBAR (or) press CTRL + SPACEBAR

Shortcuts without using ALT key:

Insert Sheet Rows/Columns: CTRL + SHIFT + +(plus symbol)

Delete Sheet Rows/Columns: CTRL + -(minus symbol)

Adding rows (or) columns without using ALT key shortcut

Deleting rows (or) columns without using ALT key shortcut

Split multiple row data in one cell into separate cells

2017-03-21T16:23:00.000+05:30

Excel Tip #008

Split multiple row data in one cell into separate cells

To force the contents of a cell onto multiple lines in the same cell, you can insert a line break within the cell by holding down the ALT key and pressing Enter.

But what if you have a worksheet with many of these cells with multiple lines and you would like to put the contents of each row in separate cells. Doing this manually by copying and pasting parts of each cell would be very slow and tedious.

So here is an option to split multiple row data in one cell into separate cells. To do this, we are going to take help from Word. This is an effective approach to break each row within a cell into separate rows in the same column.

Consider the below example where cells A1 through A4 are filled with random names where each cell occupying 5 names.

Multiple data in a single row in Excel

Copy and paste the column of data into a blank Microsoft Word document.

Copy pasted data from Excel to Word

Then copy and paste it back to Excel and each line in each cell will be split into separate cells in the same column.

Data pasted back to Excel - Transformed data

Rounding Formulas in Excel

2017-03-21T11:07:00.001+05:30

Excel Tip #007

Rounding Formulas in Excel

We can use a variety of formulas to round numbers in Excel depending on the situation. We have ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD and few more.

Before learning the tips, first lets understand various rounding formulas & what they do.

Various Rounding Formulas

Below are the examples explaining the different round functions.

ROUND formulas in Excel

Replace formulas with values

2017-03-17T10:21:00.005+05:30

Excel Tip #006

Replace formulas with values

This is one of the most useful tip that we use in everyday while we use excel. Consider you have a range of cells (or) formulas written. You want to copy the range and move it to other range of cells (or) in a new sheet.

This can be done in 2 ways - either by a keyboard shortcut (or) by a mouse shortcut. Let us consider the below range of values which are all formulas.

Range of cells with Formulas

The usual way of doing this is - copying the range, right click and then paste as values (shown in the below image).

Copy pasting as values - Usual method

Keyboard shortcut

After copying the range, press ALT + E + S which will pop up a window. Press V (denotes values) and enter, will result the range as values.

Copy pasting values - Keyboard Shortcut

Mouse shortcut

Select all the cells that contain formulas
Right click on the selection border (your mouse pointer changes to 4 sided arrow)
Right click + Drag the selection a few cells away and drop them back at original place
A menu appears. Select Copy here as values only
Copy pasted as values, done