CBA Sage (Peachtree) Accounting Support Logo



Linkedin Rss Gmail


Buy Sage 50 2022 Sage Certified Master Consultant logo

Menu
  • Home
  • Products
    • Sage 50 2023
      • Sage 50cloud Pro 2023
      • Sage 50cloud Premium 2023
      • Sage 50cloud Quantum 2023
      • Quantum Manufacturing 2023
      • Feature Comparison 2023
      • Sage Subscription FAQ 2023
    • Sage 50 2022
      • Sage 50cloud Pro 2022
      • Sage 50 Pro 2022
      • Sage 50cloud Premium 2022
      • Sage 50 Premium 2022
      • Sage 50cloud Quantum 2022
      • Sage 50 Quantum 2022
      • Quantum Manufacturing 2022
      • Feature Comparison 2022
      • Sage Subscription FAQ 2022
    • QuickBooks
    • Sage Business Care
    • Logicim XLGL
    • Checks & Forms
    • Why Sage 50?
    • Merchant Services
    • Add-Ons from Sage Partners
    • Sage 50 Free Trial
  • Services
    • Sage 50 Custom Reports
    • Consulting
    • Training
      • Training - Create your own Class
      • Webinars
    • Bookkeeping
    • Support
      • Technical Support
      • Implementations
      • Rebuilds & Repair
      • BI Reports
      • Custom Financials
      • Crystal Reports
    • Merchant Services
    • Custom Macros
  • Resources
    • Testimonials
    • Video & Tutorials
      • Sage Advisor
      • Sage 50 Tutorials
    • CBA Blog
    • EXCEL TIPS
    • 2023 System Reqs
    • 2022 System Reqs
    • Downloads
    • Newsletter Signup
    • Ask an Expert Form
    • Sage FAQ
      • Whats New In Sage 50?
    • Install Info
  • About Us
    • Why Buy from CBA?
    • Customer Success!
    • Careers With CBA
    • States We Serve
  • Contact
  • Site Map
CBA Green Bar
Resources > EXCEL TIPS

Welcome to the Computerized Business Blog please feel free to look around.


Accounting Excel Tip: Formatting Spreadsheets

6
9/6/2019

When working on a spreadsheet, there are ways to add special formatting to different cells, columns and rows with less effort.

After applying formatting to an Excel spreadsheet — such as making a cell green or adding borders to a group of cells — you can use the format painter function to copy that formatting to other cells. If you double-click on the format painter icon, you can lock the function and single-click on any parts of the spreadsheet that you want to format. When you’re done, hit escape to unlock your cursor.

For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:

  • Format numbers to include two decimal places: Ctrl+Shift+1
  • Format as time: Ctrl+Shift+2
  • Format as date: Ctrl+Shift+3
  • Format as currency: Ctrl+Shift+4
  • Format as percentage: Ctrl+Shift+5
  • Format in scientific/exponential form: Ctrl+Shift+6

By Tim Hambsch

How to Extract a List of Named Ranges in Excel

22
8/22/2019

Named ranges are an essential feature in Microsoft® Excel®.  They are easy to use in formulas and can be used anywhere in a workbook. The trick comes in when you have to remember all the defined names you have created. 

An easy way to remember all the named ranges would be to create a list on a separate worksheet that you can easily reference while creating your report. 

To obtain a list of your named ranges follow these easy steps:

  • Select a blank cell in your workbook
  • Under the Formulas menu item, select Use in Formula
  • Select Paste Names… at the bottom of the list

 

A window pops open with a list of all your named ranges.  Select Paste List. 

 

A list of all named ranges together with their respective formulas is copied to the worksheet.

 

Why should I use Named Ranges?

Named ranges are a handy feature of Excel. It can make your formulas easier to understand (and debug), help to simplify the creation of complicated spreadsheets, as well as simplify your macros.

A named range is a range (either a single cell, or a range of cells) to which you assign a name. You can use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxAmount, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

For example, Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

 

Version 2 using named ranges, making its formulas much easier to understand. Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from. Start to type a name in a formula, then double-click the name in the pick list to add it to your formula.



By Tim Hambsch

How to Remove Blank Cells in Your Excel Spreadsheet

14
9/14/2017

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. 

 


By Tim Hambsch

How To Easily Hide Zero Values in Excel

16
1/16/2017

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. 


By Tim Hambsch

Remove Hyperlinks from an Excel Worksheet

18
11/18/2016

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.  


By Tim Hambsch

Use the Excel MROUND Function to Round Off Time Values

16
11/16/2016

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.


By Tim Hambsch

How to Rearrange Data Table Columns in Excel

5
9/5/2016

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.

Picture 1

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.

Picture 2

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

Picture 3

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.


By Tim Hambsch
Move Excel Data Table
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

How to Calculate a Deposit or Down Payment in Excel

12
5/12/2016

Purchasing a car or a house is usually a pleasant experience for the buyer. However, most financial institutions require a deposit or down payment to be made towards the total purchase price.

In this tip, we show how to calculate the deposit or down payment for a car. For instance, the cost of the car could be $15,000 to be repaid over four years at 3% per period. Furthermore, you may want to keep the monthly payment at $400 per month, but need to calculate the deposit. This is how it can be done.

Applies To: Microsoft® Excel® 2010 and 2013

1. We are going to use the following formula:

=Purchase Price-PV(Rate,Nper,-Pmt)

  • PV: calculates the loan amount.
  • The loan amount will be subtracted from the purchase price to get the deposit amount.
  • Rate: is the interest rate per period. It will be divided by 4 if it is per quarter or 12 if it’s per annum.
  • Nper: is the total number of payment periods in an investment, which will be 48(4*12).
  • Pmt: is the payment made each period.

2. Place the cursor in cell C6 and enter the formula below.

=C2-PV(C3/12,C4,-C5)

3. This will give you $3,071.48 as the deposit.

1. Excel On Steroids_CalculateDepositsorDownPayments

By using this method you can calculate the deposit for a car or house using an easy formula.


By Tim Hambsch
Calculate in Excel
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

Easily Generate PivotTables on Separate Excel Worksheets

10
5/10/2016

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.

1_ExcelOnSteroids_PivotTables

4. Your PivotTable will now appear as follows:

2_ExcelOnSteroids_PivotTablesNote: 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.

3_ExcelOnSteroids_PivotTables

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.


By Tim Hambsch
Excel Pivot Table
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

Learn Some New Features In Excel 2013

28
4/28/2016
May is all about Microsoft Excel!  In this first part of a 4-part webcast series highlighting the power of Excel, register below to learn more about the  exciting features of Excel 2013, including flashfill, slicers, sparklines, automatic tables, etc. 

 

REGISTER - event over
Date and time: Thursday, May 5, 2016 2:00 pm 
Eastern Daylight Time (New York) 

  Thursday, May 5, 2016 12:00 pm 
Mountain Daylight Time (Denver)
  Thursday, May 5, 2016 1:00 pm
Central Daylight Time (Chicago)
  Thursday, May 5, 2016 11:00 am
Pacific Daylight Time (San Francisco)
Duration: 45 minutes



By Tim Hambsch
Excel 2013
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

Create an Excel Combination Chart From Scratch

26
4/26/2016

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.

combinationChartScratch1

3. Add data to the chart:

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

combinationChartScratch2

  1. 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:

combinationChartScratch3

c. Select OK.

  1. 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:

combinationChartScratch4
c. Select OK.

  1. 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.

combinationChartScratch5
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.

combinationChartScratch6
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.

combinationChartScratch7

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.


By Tim Hambsch
Excel Combination Chart
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

Learn How to Use the Goal Seek Feature

17
3/17/2016

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.

1. ExcelOnSteroids_GoalSeekFunction

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

2. ExcelOnSteroids_GoalSeekFunction

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. 


By Tim Hambsch
Excel Goal Seek
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

How to Calculate Multiple Results in Excel by Using a Data Table

24
2/24/2016

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.

1. ExcelOnSteroids_TipsAndTricks_WhatIfAnalysis

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.

2. ExcelOnSteroids_TipsAndTricks_WhatIfAnalysis

8. Click OK.

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

3. ExcelOnSteroids_TipsAndTricks_WhatIfAnalysis

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.


By Tim Hambsch
Excel Data Table
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

How to Create a Waterfall Chart

22
1/22/2016
A waterfall chart is a special type of column chart used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns.

Below are the steps to create a waterfall chart:

Applies To: Microsoft® Excel® 2010 and 2013

  1. We start by setting up the data. Select the Income and Expense Analysis worksheet.

tip-1

Note:

The value in E5 (Running Total) is Revenue (G4) – Cost of Sales (F5).

The Running Total in E7 equals Gross Profit (G6)-Fixed and Semi-fixed Expense (F7).

The value in E8 is the Net Profit and equals Running Total (E7) – variable Expenses (F8).

  1. While holding down the Ctrl key, select cells B3:B9 and cells E3:G9.
  2. Select the Insert tab.
  3. Select Column under the Charts group.
  4. Select the Stacked Column Chart.

tip-2

tip-3

  1. Right click on the Running Totals series on the chart.

tip-4

7. Select the Format Data Series menu item.

8. Select the Fill

9. Select the No fill radio button.

tip-5

  1. Select the Border Colour
  2. Select the No line radio button.
  3. With the Format Data Series window still open, select the series on your chart that reflect Income.
  4. Select the Fill
  5. Select the Solid fill radio button.
  6. Select the colour that suits the theme of your dashboard.
  7. With the Format Data Series window still open, select the series on your chart that reflect Expenses.
  8. Select the Fill
  9. Select the Solid fill radio button.
  10. Select the Red
  11. Click the Close

tip-6

  1. Right click on one of the Horizontal Major Gridlines and select

tip-7

  1. Right click the Vertical Value Axis and select Format Axis.

tip-8

23. From the Axis Options menu, select the Display Units drop down.

24. Select Millions

tip-9

  1. Select Close.
  2. Select the Waterfall Chart.
  3. Select the Layout tab then Chart title.
  4. Select above Chart.
  5. Enter Income and Expense Analysis.

tip-10

The Revenue, Gross Profit and Net Profit are whole columns and show positive values. The Cost of Sales, Fixed and Semi Fixed expenses and Variable expenses show negative values and are depicted as floating columns.


By Tim Hambsch
Excel Waterfall Chart
Sage 50 Excel Tips
Sage 50 Intelligence Training and Excel

Excel Tip: How to Change Upper/Lower Case Text in Excel

10
12/10/2015
Have you ever had to change your text from upper case to lower case (or vice versa) in Microsoft® Excel®? 

A new feature in Excel 2013 and 2016 is the Flash Fill option. This can be used to change the case of consistent text. It can also be used to split and join (concatenate) consistent text.  See the steps below:
  1. Select cell B2 and enter “JANUARY”.

flash-fill1

2. Select the Data tab and then Flash Fill.

flash-fill-2

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

flash-fill-3

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


By Tim Hambsch
Excel 2013 and 2016 Tips
Change Text in Excel
Upper Case Text in Excel
Lower Case Text in Excel

Common Microsoft Excel Errors and How to Fix Them

19
11/19/2015
Below are some common Microsoft Excel errors and how to fix them (applies to Microsoft® Excel® 2010 and 2013):

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.


By Tim Hambsch
NUM Error
NULL Error
Div0 Error
Name Error
Value Error
REF Error

Link an Excel Worksheet Name to a Cell Using the MID and FIND Functions

18
11/18/2015

Do you prepare or present reports on a regular basis?  If you use  Microsoft® Excel® to present reports the report headings can contain worksheet names and you can link worksheet names to a cell. This can be achieved by using the MID and FIND functions in Excel. This will save you the time of re-typing the worksheet names in the report headings.

In the example below you can have your worksheet names for each month in a workbook and display the worksheet names in the report heading automatically.  (This tip applies to Microsoft® Excel® 2010 and 2013.)

1. Select cell C1. Then select the Formulas

2. Select Text and then MID from the text functions list.

1.ExcelOnSteroids_TipsAndTricks_LinkWorksheetNames

3. Place the cursor in the Text box and enter CELL(“filename”,A1).

The CELL function returns information about the formatting, location, or contents of a cell.

  • It has two arguments info_type and reference.
  • Info_type: text value that specifies the information type to be returned.
  • Reference: Is a cell that you want information about.
  • “Filename” will return the path of the filename.

C:\Excel Tips\[Linking a worksheet name to a cell.xlsm]Janaury

4. Place the cursor in the Start_num box and enter FIND(“]”,CELL(“filename”,A1))+1.

  • The location within the file path name where to start extracting the worksheet name.
  • “]” instructs Microsoft® Excel® to find the position for the square bracket.
  • +1 ,adds 1 to the position returned by searching for the square bracket.

5. Place the cursor in the Num_Chars box and enter 32, then select

  • 32 is the maximum number of characters for a worksheet name.

2.ExcelOnSteroids_TipsAndTricks_LinkWorksheetNames

The worksheet name will be linked to the cell automatically plus whenever the worksheet name is changed it will automatically update the cell it is linked to! 


By Tim Hambsch
Excel MID FIND Function
Linking Excel Report Headings to a Cell

Round off Time Values in Excel Using the MROUND function

12
11/12/2015

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.

Itfs 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.
1

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.

By Tim Hambsch
Rounding time in Excel
MROUND in Excel
Round up time in Excel

Minimize Data Entry Errors by Utilizing the Calendar Drop Down List

15
10/15/2015

Ensuring data integrity by limiting data entry errors in a Microsoft® Excel® worksheet can be done by adding a calendar drop down list.  Instead of entering dates manually you can select them from the drop down list. Just follow the 10 easy steps below:

Applies to: Microsoft Excel 2007, 2010 (32-bit) and 2013 (32-bit)

The screen shot below will be used for this example.

tip-1

1. Activate the developer tab.

In Microsoft Excel 2010:

  • Select the File tab.
  • Select Options.
  • Select Customize Ribbon.
  • Tick the Developer box and select OK as per the screenshot below.
  • The Developer tab will now appear on the ribbon.

tip-2

In Microsoft Excel 2007:

  • Select the Office button
  • Select Excel Options
  • Select the Popular tab
  • Tick the Show Developer tab in the ribbon checkbox
  • The Developer tab will now appear on the ribbon.

2. Select the Developer tab.

3. Select Insert. A drop down list with icons for Form Controls and ActiveX Controls will appear.

4. Select the icon on the bottom right hand corner under the ActiveX Controls.

tip-3

5. More Controls will appear. Select Microsoft Date and Time Picker Control 6.0 (SP4)

6. Select OK.

tip-4

7. Your cursor will turn into a crosshair. Click and hold, draw a rectangle in cell C3.

8. Right-click on the Date Control and select Properties.

9. Type the cell where the date will be displayed next to linked cell as below.

tip-5

 

  • Ignore any error that you might get by clicking OK on the error message dialog box.
  • Close the properties window.
  • Select Design Mode to turn it off as per the screenshot below.

tip-6

10. When you select the date from the drop down menu the date in cell C4 will change.


tip-7

By Tim Hambsch
Minimize data entry errors
Excel and data entry errors
Calendar drop down list

How to Remove Stubborn Spaces and Characters in an Excel Cell

22
9/22/2015

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.

tip-1

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)))

tip-2

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)


By Tim Hambsch
Format spacing in Excel
Spacing issues in Excel
Remove Spacing in Excel
Remove Characters in Excel



Rss-Logo Subscribe in RSS
Email
Subscribe by Email


Feeds
CBA Blog
EXCEL TIPS & TRICKS

Latest Posts
Accounting Excel Tip: Formatting Spreadsheets ..
How to Extract a List of Named Ranges in Excel..
How to Remove Blank Cells in Your Excel Spreadsheet..
How To Easily Hide Zero Values in Excel..
Remove Hyperlinks from an Excel Worksheet..

8
Posts by Date
 2019
September (1)
•Accounting Excel Tip: Formatting Spreadsheets
August (1)
•How to Extract a List of Named Ranges in Excel

 2017
September (1)
•How to Remove Blank Cells in Your Excel Spreadsheet
January (1)
•How To Easily Hide Zero Values in Excel

 2016
November (2)
•Remove Hyperlinks from an Excel Worksheet
•Use the Excel MROUND Function to Round Off Time Values
September (1)
•How to Rearrange Data Table Columns in Excel
May (2)
•How to Calculate a Deposit or Down Payment in Excel
•Easily Generate PivotTables on Separate Excel Worksheets
April (2)
•Learn Some New Features In Excel 2013
•Create an Excel Combination Chart From Scratch
March (1)
•Learn How to Use the Goal Seek Feature
February (1)
•How to Calculate Multiple Results in Excel by Using a Data Table
January (1)
•How to Create a Waterfall Chart

 2015
December (1)
•Excel Tip: How to Change Upper/Lower Case Text in Excel
November (3)
•Common Microsoft Excel Errors and How to Fix Them
•Link an Excel Worksheet Name to a Cell Using the MID and FIND Functions
•Round off Time Values in Excel Using the MROUND function
October (1)
•Minimize Data Entry Errors by Utilizing the Calendar Drop Down List
September (2)
•How to Remove Stubborn Spaces and Characters in an Excel Cell
•FREE e-book: Excel PivotTables and PivotCharts
August (1)
•Excel Tip: Creating First and Last Name Columns from a Full Name Field
June (1)
•Excel Tip: Editing the Same Cell in Multiple Worksheets
April (1)
•Using the TEXT Formula for Sage Intelligence Reporting
February (1)
•Calculating the Frequency of Specific Values Within a Range of Values
January (1)
•FREE WEBINAR: EXCEL TIPS FOR BETTER BUSINESS REPORTS

 2014
August (1)
•DATEDIF and TODAY Functions Combined
June (1)
•Excel Tips: Conditional Formatting… Finding What You Need in a Pile of Data

 2013
August (1)
•Using Slicers in Excel 2010
May (1)
•Using the TRANSPOSE Function in Excel
January (1)
•Using the DSUM Function to Add Based on Multiple Criteria

 2012
October (1)
•VLOOKUP with the Approximate Match
September (1)
•Sharing Slicers Between PivotTables in Excel 2010
August (1)
•Using a Data Entry Form in Excel
July (1)
•How to use the SUBTOTAL Command
May (1)
•How to use the SUMIFS function in Excel
April (1)
•How to Display Filtered Pivot Table Data on Separate Worksheets


Tag Cloud
  • Sage 50 intelligence training and excel
  • Sage 50 excel tips
  • Excel 2013
  • Sage 50 reporting
  • Peachtree reports
  • Name formula columns in excel
  • Format spacing in excel
  • Using excel for sage 50 reporting
  • Calculate in excel
  • Move excel data table
  • Excel combination chart
  • Sage business intelligence
  • Excel goal seek
  • Calculate days in excel
  • Excel waterfall chart
  • Remove spacing in excel
  • Excel 2013 and 2016 tips
  • Pivottables and pivotcharts
  • Change text in excel
  • Editing text in excel worksheets



Share
RSS feed
Atom feed
Bookmark
Google
Delicious
Digg
MySpace
Facebook
Diigo
Yahoo
Stumble
Reddit
My Aol
LinkedIn

Bookmark and Share
  Powered by WorldWide Merchant


© 2023 Computerized Business blog all rights reserved.


CBA Green Bar

  • Home
  • Products
  • Services
  • Resources
  • About
  • Contact
  • Sage 50 2023
  • Sage 50 2022
  • VersAccounts ERP
  • Checks & Forms
  • Sage 50 Support
  • Peachtree Support
  • Tech Support
  • Sage 50 & Peachtree Training
  • Management Consulting
  • Bookkeeping
  • Merchant Services
Computerized Business Associates
 
1009 Northwood Drive
Galesburg IL 61401
480-945-4455 or 800-339-8224
info@computerizedbusiness.com
Sign Up For Our Newsletter!

CBA Peachtree Newsletter Signup Image
Get tips and news from Sage 50 (Peachtree) experts in your inbox
Master Certified Sage Consultants
Arizona, Nevada, California, Nationwide, Worldwide

This website is owned by Computerized Business Associates. Computerized Business Associates is independent from Sage and is not authorized to make any statement, representation, or warranties or grant any license or permission on behalf of Sage regarding any product, service, or website content. Certain materials made available on or through this website are owned by Sage and cannot be used without the prior written permission of Sage.
© 2023 Computerized Business all rights reserved.