excel training microsoft

excel training microsoft - Pivot tables

Resolved · Low Priority · Version Standard

Pivot tables

Neville has attended:
Excel Intermediate course
Excel Intermediate course

by - delegate Neville [2 posts] (2007 Sep 11 Tue, 16:36) Reply

how do you group individual dates into months and years?

Version	Date	Location	Places available	Book	Next place rate:
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Dec 3 Wed	Bloomsbury	0	FULL
2003	2008 Dec 5 Fri	Bayswater	1	book now	£195	£205
2002/XP	2008 Dec 10 Wed	Tooting	0	FULL
2003	2008 Dec 10 Wed	Bloomsbury	0	FULL
2007	2008 Dec 10 Wed	Bloomsbury	2	book now	£240	£245
2003	2008 Dec 12 Fri	Southwark	6	book now	£214	£230
Full Schedule: See all 96 Excel Advanced course dates. Bookings currently available until 24th November 2009.

RE: pivot tables

by - trainer Amanda diamond contributer [1240 posts] (2007 Oct 1 Mon, 13:52) Reply

Hi Neville

Thanks for your question.

To group dates in a pivot table:

1. Right-click the Date field button.
2. Choose Group and Show Detail, then Group
3. In the Grouping dialog box, select one or more options from the 'By' list.

To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes

4. Click OK

I hope this helps.
Amanda

· How to Create Better Excel Spreadsheets: Part Three
· How Excel Training Can Improve Your Company's Sales Process
· 3 Reasons why Your Small Business Office Needs Microsoft Excel Training
· Advanced Excel Training - takes you to the next level
· How Small Businesses Profit from Outside Excel Training

Excel tip:

Creating a range of monthly payments as text

You could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle.

The text that would be "between Xamount and Yamount".

Here is how to do it.

1 Use the PMT function to get your monthly payments figure or whatever frequency of payments that you choose he start range.

See PMT under Excel Help

2. Nest these in the ROUND function to round decimals see ROUND under Excel Help

3. Concatenate this using "&" and concatenate " to " and concatenate "Between ".

4. Concatenate the above to PMT function for the end range

ie.

="Between "&ROUND((PMT1),decimal places)&" and "&ROUND((PMT2),decimal places)