19 November 2013

Auto Sums and Custom Number Formats

Excel iconIn my latest Excel tutorial in the Tips section, I am showing four ways to make Excel calculate sums and one way to add a custom number format. I am using Excel 2013 in the demo, but what I explain is applicable to earlier versions of Excel also.

Series of Excel demos
In the Tips section you can find demos about SharePoint, Outlook and Excel, and I have also given a few tips on other platforms. Some of the Excel tips are about how Excel tables and charts can be incorporated in SharePoint, but there are also a series of tips on Excel calculations and formulas. The demo below belongs to that group.

AutoSum button
Excel AutoSum buttonWhen you want to make Excel calculate for you, you can use formulas like =A1+A2+A3 or =SUM(A1:A3), but you can also make it easy and use the AutoSum button. However, you should be aware that if no cells are selected but the one where the sum should be placed, Excel will make a guess. This guess is qualified but not always correct, so you have to check it.

To be on the safe side, use the AutoSum button when you have selected the cells to be summarized. Then Excel will calculate the sum of the selected cell in a correct way.

Custom number format
Excel gives many options for displaying numbers, for example as percentages, with a currency or as a date. You may also create your own number format. Excel gives a lot of examples to select from and maybe change, but you can also write it in like I do.

Excel Custom formatCustom number formats include a combination of hash marks and zeros used as placeholders. In my example the zero means that the unit should always be displayed, while the hash marks mean that the tens and hundreds should only be shown if they have a value. For example "002" will be shown as 2 in the cell, and "222" will be shown as 222.

HelpDesk OSP icon
Excel reports

The reason why I have been recording so many Excel tutorials this year is that most solutions have Excel statistics reports. Calendar Browser icon Each of the products has its own default reports that give a lot of information on how the product has been used. It is also possible to create custom reports on the same data.

Folder HelpDesk icon All the standard Excel feautres can be used with these statistics reports, and I hope users will make the most of them. Excel gives excellent possibilities when it comes to analyzing data and to show results in graphs and charts.Kanban Task Manager icon

By Peter Kalmstrom
CEO and Systems Designer Business Solutions

No comments:

Post a Comment