How to Split worksheet into panes in Excel 2013

E-BOOK AND DVD

COMING SOON....WAIT FOR THIS PAGE

Solutions for VLOOKUP not working or showing #N/A

Major reasons for your VLOOKUP not working properly or showing error (#N/A or #REF!) could be:

1. The Lookup values have Trailing space at the end
2. The Numbers entered have been formatted as Text
3. You forgot to reference lock your table array
4. The file or table array to lookup has been deleted

So, let's tackle these issues and get solution for each of them.

1. Lookup values having trailing space at the end

Let's look at the below VLOOKUP example. Although, the values are available in the source table, the lookup table is not returning values for some of them. The problem is not directly evident. To see the problem, go to the lookup value in the source table and click the cursor at the very end of value. The extra space will be evident at the end of the input.

Solution: It is very simple. Please remove the extra space by pressing "Backspace" in edit mode.

2. The numbers entered have been formatted as Text (shows #N/A error)

In case none of the lookup value are not returning results (#N/A is appearing), it may be possible that the formatting of source is incorrect (the numbers will appear to be aligned left).  Click on the lookup reference of the data set and check in the formula bar if there is an apostrophe before your entry.

This is sometimes intentionally done to populate leading zero in the number (although a better way is through custom formatting...!!).

Solution: Again, the solution is very simple. Multiply all your lookup values by 1. This will convert all of them into number format (the numbers will get automatically aligned right).

3. You forgot to reference lock your table array

This is very common problem that occurs when we drag the VLOOKUP formula after entering it in one cell. Often we forget to lock the table array and as a result the lookup values you are trying to match are no longer part of original lookup array (since the lookup array also shifts down when we drag it).

Solution: Before dragging the VLOOKUP formula, press F4 key to lock lookup array (in edit mode)

4. The file or table array to lookup has been deleted (shows #REF! error)

Sometimes the source data used for VLOOKUP function gets deleted or sheets are improperly moved without reference. In such case VLOOKUP shows #REF! error.

Solution: Try and locate the source data and verify the linking of VLOOKUP formula again

How to Freeze Panes in Excel 2013

Freeze Panes is an excellent tool in Excel 2013 for viewing different parts of the same worksheet that normally cannot be seen together. Generally, this tool is used to freeze headings in top rows and first columns so that it is visible at all times while scrolling the worksheet.

This is particularly useful in cases where we have large spreadsheet with too many rows and columns that do not fit on the screen.

Let's understand this on the basis of an example. The spreadsheet contains financials of a company with multiple years and several line times. We want to view heading and line items at all times. To create and freeze these panes, follow these steps:

1. Position the cell cursor in cell B2 (intersection cell)

Excel will freeze the rows above this cell and columns to the left of this cell.

2.  "VIEW" tab in ribbon>>>Freeze Panes dropdown>>>Freeze Panes [Shortcut: Alt+WFF]

Excel will automatically show black line on the top row and left column of the selected cell indicating frozen rows and columns.

FREEZE TOP ROW or FIRST COLUMN:

Excel also provides customized option to freeze only top row or first column of the worksheet, regardless of where the cell cursor is located. Follow the below steps:

"VIEW" tab >>> Freeze Panes dropdown >>> Freeze Top Row [Shortcut: Alt+WFR]

"VIEW" tab >>> Freeze Panes dropdown >>> Freeze First Column [Shortcut: Alt+WFC]

UNFREEZE PANES:

To unfreeze any of the panes, there is one stop solution. Go to:

Go to: "VIEW" tab >>> Freeze Panes dropdown >>> Unfreeze Panes [Shortcut: Alt+WFF]

This will automatically remove the black lines from the worksheet, indicating that Excel has unfrozen them.

IMPORTANT NOTE:

• There is no default option to freeze multiple rows without freezing any columns. To do this, go to Column A and place the cell cursor below the rows you want to freeze and then go to freeze panes (Alt+WFF).
• Similarly, there is no default option to freeze multiple columns without freezing any rows. To do this, go to Row 1 and place the cell cursor to the right of columns that needs to be freezed and then click on freeze panes (Alt+WFF).

How to use OFFSET function in Excel 2013

The OFFSET function returns a cell or range of cells that is a specified number of rows and/or columns from the reference cell.

IMPORTANT NOTES:

• If either of the “rows”, “columns”, “height” or “width” components are left blank, Excel will assume its value to be zero. For example, if the formula is written as OFFSET(D20, , 1, , ) Excel will interpret this as OFFSET(D20, 0, 1, 0, 0). This can also be written as OFFSET(D20, , 1) since “height” and “width” can be omitted.
• Note that if “height” and “width” are included in the formula, they cannot be equal zero or a #REF! error will result.