This easy Excel tip will show you how to delete a number of cells and then shift all the cells so that there are no blank cells without accidentally deleting any spreadsheet data.   (This applies Microsoft® Excel® for Windows 2010, 2013, and 2016.)

As you can see above, partial data has been removed leaving blank spaces in between the data.

To delete the blank cells and move all other data, follow these steps:

1. Highlight the cells with the data and blank cells in the worksheet.

2. On the Home Ribbon, under the Editing tab, click on Find and Select, and then Go To Special from the drop-down list.

3. Select Blanks and click OK.

Note that Excel now highlights all the blank cells.

4. Right click on any of the highlighted cells and select Delete.

5. Select Shift Cells Up and click OK.

Excel will now delete all the blank cells and the data order is maintained. 

In order to have neat looking reports in Microsoft® Excel®, you may choose to hide all zero values. For this Excel tip we show how to hide zero values for selected cells by using the custom number format. The hidden zero values appear in the formula bar when a cell is selected. Values that change to non-zero digits will be displayed in the cell range.

This tip applies To: Microsoft^® Excel^® 2013 and 2016.

1. Select a data range 
2. Then press Ctrl+1 and select the Number tab then Custom.
3. In the Type box, enter 0;;;@ then select OK.

To display hidden values:

1. Select a data range
2. Then press Ctrl+1.
3. Select the Number tab then General.
4. Select OK.

The cells with zero values will display blank cells. This will give the report a neat look and make it more presentable. 

Below is a quick and easy way to remove hyperlinks from an Excel worksheet. This is done by applying a code to your worksheets that will save you time and avoid having to manually remove the hyperlinks.

This simple tip will remove all the hyperlinks from your Excel worksheet in one click! 

(Applies To: Microsoft^® Excel^® 2007, 2010, 2013, 2016)

1. Ensure you are on the worksheet where you want to remove the hyperlinks.

2. Press ALT+F11 to open the Visual Basic Editor.

3. From the Menu bar, select Insert Module and copy and paste the codes below into the window on the right.

Sub RemoveHyperlinks()

Activesheet.Hyperlinks.Delete

End Sub

4. From the Menu bar, select File, Close and Return to MS Excel.

5. Press ALT + F8 to view a list of Macros.

6. Select the Macro, RemoveHyperlinks, and then select Run.

(Make sure that your Macro Settings are not disabled under the Macro Settings Option.)

To set your Macro security level, follow the steps below:

• On the Office/File tab, select Options.
• On the left side of the dialogue box,select Trust Center.
• Select the Trust Center Settings button.
• On the left side, select Macro Settings.
• Select Disable all macros with notification.

All the hyperlinks have now been removed.  

This Excel tip is a great tool if you work with units of time values, especially HR personnel, IT security professionals, stock controllers etc.

(Applies To: Microsoft^® Excel^®2010 and 2013)

1.  Start by formatting numbers to the time format. (See image in #3 below.)

• Highlight the range B3:B9.
• Right click and select format cells.
• Select custom under the number tab.
• Select h:mm:ss then select OK.

2.  To round off to the nearest hour.

• Place the cursor in cell C3.
• Type =MROUND(B3,”1:00″).
• Press enter and copy the formula down.

3.  To round off to the nearest minute.

• Place the cursor in cell D3.
• Type =MROUND(B3,”0:01″).
• Press enter and copy the formula down.

The time values have been rounded to the nearest hour or minute. Only minutes or seconds that are greater than or equal to 30 are rounded off.

As with most commands in Microsoft® Excel®, there’s an easy way and a long way to rearrange columns in a data table. The long way would be to highlight a column, cut it, and then paste it where you want to move it to. Here's a quicker way to do this.  (Applies To: Microsoft^® Excel^® 2010, 2013 and 2016.)

1.Below is a two column data table.

2. If you wanted to move the “Amount” column before the Sales Rep column,  you would simply select the column header, then press ctrl + shift+ down arrow.

3. Then, click and hold the green outline of your range (you must see a 4 arrow cross before you click and hold).

4. Finally, drag your column to the left and release the click.

And that’s it! This is an easy yet effective method to rearrange columns in a data table, saving you time and improving the way you work in Excel.

You have taken time to create a product sales PivotTable and now you would like to analyze the data. You want to analyze the sales per branch by quickly generating PivotTables for each branch to be placed on separate worksheets.

In this tip, we take you through a few easy steps as we show you how this process can be easily automated.

Applies To: Microsoft^® Excel^® 2007,2010,2013,2016.

1. Open the practice workbook and select any cell within the data list.

2. Select Insert–PivotTable and then select OK.

3. An empty PivotTable will open on a new worksheet. Refer to the PivotTable field list and place the fields as follows.

a. Branch: Filters Area.

b. Product Name: Rows Area.

c. Product Sales: Values Area.

4. Your PivotTable will now appear as follows:

Note: Branch in the Filters Area.

5. You now want to display the Branch details on individual worksheets:

a. Go to the Analyze Tab, select the drop down arrow next to Options, and select Show Report Filter Pages and select OK.

6. The individual details will be displayed on separate worksheets.

Your sales data for the different branches will be extracted and placed on separate worksheets, thus making it easier to view and analyze your sales data at a glance, which allows for better decision making.

After you have created your Microsoft^® Excel^® spreadsheet, you can visually represent the worksheet data by creating a chart. Charts often make your data clearer and easier to understand.

If you find that the data is not in sequence or in a group of cells, you may need to create your own series, specifying the labels and values. Data can be extracted from different worksheets and even workbooks.

TIP: To create a quick chart, select any cell within the data range and press F11.

In this tip, we are going to create a combination chart from scratch showing the difference between the Sales and Purchases.

Applies To: Microsoft^® Excel^® 2007,2010 and 2013

1. Open the practice workbook.
2. Create a blank line chart:

a. Select any blank cell on the worksheet.
b. From the Insert tab, in the Charts group, select Line.
c. Select 2-D Line.

3. Add data to the chart:

a. Select the blank chart.
b. From the Design tab, in the Data group, select Select Data.

4. To create the Sales series:
   a. Select the Add button in the Legend Entries (Series)
   i. Click in the Series name text box and select cell A4(Series name).
   ii. To highlight the data range, click in the Series values text box.
   iii. Clear the contents and select the range C5:C16.b. The Edit Series box should contain the following:

c. Select OK.

5. To create the Purchases series:
   a. Select Add.
   i. Click in the Series name text box and select cell A21(Series name).
   ii. To highlight the data range, click in the Series values text box.
   iii. Clear the contents and select the range C22:C33.b. The Edit Series box should contain the following:

c. Select OK.

6. Select the Edit button in the Horizontal (Category) Axis Labels.
   a. To display the months on the horizontal line, highlight the data range A5:A16 or A22:A33.
   b. Select OK.

7. Add a chart title:
a. Select the chart as below.

b. Enter the heading as Sales Vs Purchases.

8. Move the chart to a new worksheet:
a. From the Design tab, in the Location group, select Move Chart.
b. Select New Sheet and name the worksheet as shown below.

c. Select OK.

9. Right click on the Sales series.
10. Select change series chart type.
11. Select the down arrow under chart type at the bottom of the window.
12. Select Clustered column and select OK.

You can place charts in different worksheets to make it easier for you to analyse your information. The chart that we have created compares Purchases to Sales, which can quickly help you with decision making.

If you know the result you want from a formula, but you are unsure of what input value the formula requires to get that result, you can use the Goal Seek Feature in Microsoft^® Excel^®. Goal Seek takes a result and determines possible input values that produce the result you are looking for. Data analysis can be enhanced with the use of Excel’s What-If analysis.

You can use the Goal Seek to get a preferred result by adjusting one of your input values. Let’s say you own a hotel and would like to know the number of rooms to rent out in order to make a profit of $20,000. You can use Goal Seek to achieve the result by adjusting the rooms to rent out variable.

Applies To: Microsoft^® Excel^® 2013 and 2016

Apply these easy steps to use Excel’s Goal Seek feature:

1. Select the Goal Seek

2. Select cell B4. Note the formula used to calculate the required profit. (Rooms rented *Price per room )- Expenses.

3. Select What if Analysis under the Data tab and then Goal Seek.

4. In the Set cell box, select cell B4, the cell with the formula you want to resolve.

5. In the To value box, enter 20000.

6. Place the cursor in the By Changing box and select cell B5.

7. Select OK twice. The result will be as below.

As you can see the number of rooms to rent out were easily calculated without using a complex formula. It is important to remember that the Goal Seek option can only adjust one variable at a time. 

When you examine business data in Microsoft^® Excel^®, you may want to discover the result of formulas if you provide different inputs. You can calculate the results of those changes by using a data table which makes it easy to examine a range of possibilities at a glance.

You can create one or two variable data tables, depending on the number of inputs and formulas that you want to test.

Use a one variable data table if:

• You want to see how different values of one variable in one or more formulas will change the results of those formulas.

Use a two variable data table to:

• See how different values of two variables in one formula will change the results of that formula.

In this example, we use a two variable data table to calculate the projected sales for 2016. The input values are possible sales and expense growth.

Applies To: Microsoft^® Excel^® 2010 and 2013.

1. For a two variable data table, the copy of the original formula must be entered at the intersection of the row and column input values. At cell B7, type =B5.

(The formula in B5 is =(Sales 2015+( Sales 2015*Growth 2016)-( Sales 2015*Expense 2016)).

2. Type one list of input values in the same column, below the formula.

3. Enter the second list in the same row as the formula, to its right.

4. Select the range of cells that contains the formula, both the row and column of values, and the cells in which you want the calculated values. (B7:H15).

5. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.

6. In the Row input cell box, enter the reference to the input cell for the input values in the row.

7. In the Column input cell box, enter the reference to the input cell for the input values in the column.

8. Click OK.

9. Format the values in the range C8:H15 to the currency of your choice.

A table displaying the projected sales for 2016 has been generated. The table shows the projected sales for 2016 based on possible sales and expense rates.

1. Select cell B2 and enter “JANUARY”.

2. Select the Data tab and then Flash Fill.

3. The months will all be converted to upper case.

The months have been quickly and easily converted to upper case without the use of complex formulas.

1. ###### error.

a. The column is not wide enough to display all the characters in a cell.

b. Extend the width of the column.

2. # Div/0! error.

a. Excel displays this error when a number is divided either by zero (0) or an empty cell.

b. Change the divider to a value that is not equal to 0.

3. #Name? error.

a. The text in a formula is not recognized by Excel.

b. This is caused by misspelling of function names.

c. For instance =su(A1:A7) will result in the #Name? error.

d. Correct the error by entering =sum(A1:A7).

4. #Value! error.

a. This error will be displayed if the formula includes cells that contain different data types.

b. = B1(85) + B2(75) +B3(data) will result in the #Value! error message.

c. Replace B3(data) with a numeric value and the error will be corrected.

5. #REF! error

a. The error will be displayed when a cell reference is not valid. Deleting cells that were referred by other formulas will cause this error.

b. Refer the cells to the correct ranges and the error will be corrected.

6. #NUM! error.

a. The formula or function contains invalid numeric values.

b. Using $ ,% symbols with the number can result in this error.

7. #NULL error.

a. Excel displays this error when you specify an intersection of two areas that do not intersect (cross).

b. The intersection operator is a space character that separates references in a formula.

c. =Sum(A1:A2 C3:C5) returns the #NULL error because the two ranges do not intersect.

d. =SUM(A1:F1 B1:B10) will return the correct reference.

Working with time values comes with its own challenges. One of the issues is how to round off time values from seconds to minutes or minutes to hours.

You can use the MROUND function to round off time values. The MROUND function returns a number rounded to the nearest instance of a specified multiple.

It�fs easier to manipulate time values if they are rounded off. So, if you work with time values you will find this tip useful, especially HR personnel, IT security professionals, stock controllers etc.

Applies To: Microsoft^® Excel^® 2010 and 2013

1.Start by formatting numbers to the time format.  (See the Excel layout below.)

• Highlight the range B3:B9.
• Right click and select format cells.
• Select custom under the number tab.
• Select h:mm:ss then select OK.

2.To round off to the nearest hour.

• Place the cursor in cell C3.
• Type =MROUND(B3,�h1:00�Ť).
• Press enter and copy the formula down.

3.To round off to the nearest minute.

• Place the cursor in cell D3.
• Type =MROUND(B3,�h0:01�Ť).
• Press enter and copy the formula down.

The time values have been rounded to the nearest hour or minute.  Only minutes or seconds that are greater than or equal to 30 are rounded off.

If you have ever had problems sorting, filtering, or manipulating data in a Microsoft® Excel® workbook and realized that it was caused by stubborn leading spaces in the cells, the following steps will show you how to get rid of the spaces quickly next time.

If you’re wondering what causes the spaces, here are two possibilities:

• You may have mistakenly inserted extra spaces in your data by pressing the space bar more than once; or
• You may have imported data from external sources which has embedded characters in the text that may not be easily noticeable making the results difficult to understand.

The next time you have this issue you can quickly remove unwanted spaces and characters using a combination of the TRIM and SUBSTITUTE functions. The TRIM function removes leading and trailing spaces in data but not characters. The SUBSTITUTE function replaces non-breaking characters with characters that the TRIM function can remove.

1. To remove leading and trailing spaces from the example workbook; the TRIM function will be used.

a. Select cell C2.
b. Type the formula =TRIM(B2).
c. Press Enter and copy the formula down.

2. As you can see the spaces have not yet been removed. This is because the TRIM and SUBSTITUTE functions must be nested.The stubborn characters set number 160 will be replaced with 32 then can be removed by the TRIM function.

Note:

• In order to transmit information between computer systems a standard system that assigns numbers to letters of the alphabet, digits and characters was developed.
• Therefore the character set number 32 represents a space.
• The character set 160 represents a non-breaking space character that can’t be removed by the TRIM function. It is commonly used in web pages.

a. Select cell C2 and delete the formula.
b. Enter the following formula and then copy it down:

=TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))

The stubborn leading spaces and characters will be removed so you can easily manipulate your data. Essentially, the SUBSTITUTE function in our example replaced the non-breaking character with a space which the TRIM function can remove.

(Appies to Microsoft® Excel® 2007, 2010 and 2013)

11 tips on Excel PivotTables and PivotCharts!

• Slicers
• Trendlines
• Timelines
• Rank Smallest to Largest
• PivotTable Groups
• PowerPivot add-in
  
  DOWNLOAD NOW!

If you need to split your employee names (or FFL player names!) in Excel as shown below:

Here are the formula’s:

To understand what these formulas are doing let’s break them down.  There are 4 individual Excel functions being used:

1.       LEFT(text,[num_chars]) – returns the left most number of characters from the start of a text string: if all last names were the same length we could just use the formula =LEFT(A2,7) and it would return Johnson as the Last Name in the first row of the example above

2.       RIGHT(text,[num_chars]) – returns the right most number of characters from the end of a text string: if all first names were the same length we could just use the formula =RIGHT(A2,6) and it would return Calvin as the First Name in the first row of the example above

3.       LEN(text) – returns the number of characters in a text string – the LEN(A2) segment of the formula used to return the First Name would return the number 15 if used by itself in the first row of the example above

4.       FIND(find_text, within_text) - returns the starting position of one text string within another text string – the FIND(“,”,A2) would return the number 8 if used by itself in the Last Name column of the first row of the example above and FIND(“ “,A2) would return the number 9 if used by itself in the First Name column of the first row of the example above

Therefore if we breakdown the First Name formula (since it’s the more complex of the 2) it is doing the following:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

   Where A2 = Johnson, Calvin

   Where LEN(A2) = 15 (Johnson is 7 characters + 1 for the comma + 1 for the space + 6 for Calvin)

   Where FIND(“ “,A2) = 9 (the space is the 9th character from the left)

We then have RIGHT(A2,6) which returns the 6 characters from the end of the string which equals Calvin!  And now this formula can easily be copied down your column to return the First Name for everyone on your list.

A little challenging to put into words but you’ll find it simple to use once you set it up!