October 2017 Tip Of The Month

October 2017 News & Tips

NEWS		SAGE 50		EXCEL
Support for version 2017 will end soon. Payroll users should upgrade to v2018 by 11/30/2017.		Use the 1099 Vendor Report now to help make year ends tasks easier.		The "Format as Table" button does a lot more than just provide a formatting shortcut.

News

End of Support for Sage 50 2017: I haven't received official word from Sage, but based on their usual schedule, I expect them to stop supporting version 2017 on 11/30/2017. That means if you use payroll, you need to upgrade to version 2018 by the end of November to be sure that payroll will continue to work properly. The upgrade is already included in the cost of your annual renewal. If you want help with that upgrade, please don't wait until November 30th (Dec 1st an hour before payroll is due) to call me.

Of course I will continue to assist users on version 2017 or older. But using payroll, or other Sage add-on services does require you to stay on the current version of Sage 50.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
scollins@iqaccountingsolutions.com
www.iqaccountingsolutions.com

A complete archive of these tips is available at http://www.iqaccountingsolutions.com/blog

Sage 50/Peachtree Tip - 1099 Vendor Report, It's not just for January.

There's just over two months until the end of the year. If you'll have to issue 1099s, now is a good time to make sure you have everything in order. Sage 50's 1099 vendor report is a good tool to help you with that task. Go to the Reports & Forms menu and choose Accounts Payable. 1099 Vendor Report will be at or near the top of the list of reports.

The standard report will list all current year payments to 1099 vendors, unless those payments went to GL accounts that have been excluded from 1099s. It will also show what kind of 1099 the vendor will receive and what their total is for the year. I would recommend that you add Address and Tax ID Number columns to the report. Just click on the Columns button and check the Show box next to those items. Adding the phone number will make it easier If you need to confirm information on the report. To make the report easier to read you can use the Move Up and Move Down buttons to change the order of the columns and the Column Break check boxes to control which items are grouped together in a column.

If anyone is missing, or the amounts don’t look right, Make sure their 1099 Type is set correctly in Maintain > Vendors. And review the 1099 Settings tab in Maintain > Default Information > Vendors.

Forward To A Friend

Excel Tip - Taking Advantage of Tables

In the general sense, a table is just data that you have arranged into columns and rows. But in Excel, when you use the Format As Table button to define an area on your spreadsheet as an Excel table, there are several things you'll be able to take advantage of that make entering and working with your data a little easier.

You can define a table before or after you have started entering data. If you're starting from a blank worksheet, select the area you'll be using for your table. Don't worry about the number of rows, that will be adjusted later. If you already have data on your worksheet, just make sure your cursor is within the area you want to define as a table. Click the Format as Table button (It's located in the Styles section of the Home ribbon) and choose a formatting style from the list. In the window that pops up. You can adjust the area selected for the table in the "Where is the data for your table" field. Also choose whether or not your table has headers. Normally that box should be checked. Clearing it will tell Excel that the first row in your table is data, not column headings. Click OK. You've just defined and formatted a table. So what does that do for you?

Easier Data Entry - When you enter something in the last column of a table, pressing the tab key will take you to the first column of the next line instead of to the next column.

Easily Locate Information - You'll see a down arrow button on the right edge of each column heading (the header row). This button gives you quick access to filtering and sorting your table.

Shaded (Banded) Rows - If you miss green bar paper you'll love this. Many of the table formatting options have alternating light and dark shaded rows to make it easier to follow each line. And when you reach the end of the formatted area, Excel will automatically extend the formatting as you enter data in the next row. Formatting is also adjusted if you insert rows or columns. On the Design tab of the ribbon there's an option for banded columns if you prefer to have the columns shaded instead of rows.

Formulas Automatically Copied to Each Row - When you enter a formula in a table, it will automatically be copied to each cell in that column. It will even be placed in new rows as you add them.

Easy to Read Formulas - If you enter a formula in a table by clicking on the cells (for example, type "=" then click on cell B2, type "+" and click on cell C2), your formula will use the column headings instead of the cell reference. For example if column D is "Qty" and column E is "Unit Price" you might end up with a formula that reads =[@Qty]*[@[Unit Price]] instead of =D2*E2. Microsoft calls this a "structured reference" and "=D2*E2" is called an explicit reference. If you rename a column, the formulas will self adjust.

Flexible Total Row - On the Design tab of the ribbon (visible only when you are in the table) in the Table Style Options there's a check box for Total Row. Check it and a row for totals will be added at the bottom of your table. By default only the last column will have a total, but click in any cell on that row and a button will appear that lets you choose a total type for that column. Choices are:

None
Average
Count
Count Numbers
Max
Min
Sum
StdDev
Var
More Functions (this will open the function wizard, letting you select from all of Excels functions)

Easy to Resize - As I mentioned before, when you get to the end of a table, entering data on the next line will automatically expands the table, including formatting and formulas. The same is true for columns. But you can also resize a table by dragging the small triangle shaped handle that's in the bottom right corner of every table. Or, on the Design tab of the ribbon you can click the Resize Table button if you want to type in a cell range for your table.

If you're like me you've ignored tables in Excel because you were already comfortable doing most of these things other ways. But when you look at everything tables offer (especially easier data entry) it makes sense to change your routine and take advantage of tables.

Forward To A Friend

If you know other Sage 50/Peachtree users, do them (and me) a favor and pass this along to them. If you are someone that this has been passed on to, and would like to be added to my email list, you can subscribe for free at www.iqaccountingsolutions.com/signup.html or send me an email at newsletter@iqaccountingsolutions.com. If you need to change your email address or would like to unsubscribe, you can use the links at the end of this email.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
10611 E 17th Place
Tulsa, OK 74128