Microsoft Excel – showing field names as headings rather than “Row labels” in Pivot tables by default

For many versions of Microsoft Excel I found it annoying that it said Row labels and Column labels in a Pivot Table instead of the actual field headings.

In Excel 365 this can now eventually be changed to show field headings by default.

To do so, from within Excel itself, go to File – Options. Click Data.

Click Edit Default Layout.

From the Report Layout dropdown, select either Show in Outline Form or Show in Tabular Form.

Click OK twice.

In earlier versions, by default if you create a pivot table, instead of showing the field names, it will say row labels and column labels.

To see the field names instead, click on the Pivot Table Tools Design tab, then in the Layout group, click the Report Layout dropdown and select either Show in Outline Form or Show in Tabular form. The relevant labels will then be displayed.

Find out more about our Excel training in the London area.

Find out more about our Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Excel – Using Quick Analysis for % of Totals and Cumulative Totals

To calculate % of Totals in Excel, you would normally have to use absolute cell references or name the Total cell. Similarly for cumulative totals, you would have to create the relevant formula. Provided you have Excel 2013 or a later version than that, you can save the effort of using formulas by using the Quick Analysis tool.

Ensure you have a spare column next to the data for the answers to be inserted. You do not need to have created the total.

Select the data to be used.

At the bottom right, you will see the Quick Analysis button. Click on it.

You can do several things with Quick Analysis including Conditional Formatting, creating Charts, creating Tables and creating Sparklines. Today I am concentrating on some of the Totals options.

Click on Totals along the top of the Quick Analysis section. Further buttons will be shown. The first group are for doing calculations across rows. We are looking at columns here so scroll to the right.

To create percentages of the total, click % Total.

To calculate a cumulative total, click Running Total.

Find out more about our Excel training in the London area.

Find out more about our Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Find out more about our online training.

Microsoft Word – Order of Styles in Style Gallery

If you are using styles in Microsoft Word, you may just want to see the styles you want to use and you may wish to delete some of the styles you can see.

Deleting styles from the Gallery is easy – right-click on the style you no longer want to see and select Remove from Style Gallery.

Ordering styles takes a bit more effort.

To order styles in the Style Gallery:

1. Click on the dialog box launcher at the bottom right of the Styles group on the Home tab. The Styles pane will be displayed.
2. Click the Manage Styles  button. The Manage Styles dialog box will be displayed.
3. Click the Recommend tab.
4.  If you want the styles to be in the same order for all new documents based on this template, select New Documents based on this template.
5. Select the Style  you want to move, then click Move Up or Move Down.
6. Continue for any other style you want to move.
7. When you have finished, click OK. The Style Gallery should then be adapted to your needs.

Find out more about our Microsoft Word training in the London area.

Find out more about our Microsoft Word training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Excel – due date end month following invoice date

I was doing some online virtual Excel training last week and the client asked how to work out the due date on invoices if it is the end of the following month after the invoice date. There is an Excel function EOMONTH which will do just that. The syntax is

=EOMONTH(Start date, no of months), where no of months is how many months from the start date.

So if you want the end of the month following the Invoice date and the Invoice date is in cell A2, the formula is =EOMONTH(A2,1)

At present, we are training online.

Find details of our interactive training here.

Find more details of our Excel training in the London area.

Find out more details of our Excel training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Office – creating chart showing only weekdays

I have some Excel data that only shows data for week days as I do not work weekends. However, when I came to chart it, it put the weekend dates in, showing blanks. This wasn’t what I wanted.

Right-click on the horizontal axis showing the dates and select Format Axis. Under the Axis Options, for Axis Type, select Text axis. The Chart then just displays the data for the weekdays.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Hampshire and Somerset.

Microsoft Excel – arranging countries into regions in a PivotTable

If you are a global company and are analysing sales by region, even if the name of the region isn’t in the raw data, you can group countries together by region as long as you know the appropriate regions to group them in to.

In the example below, I have various countries and their last year’s sales. I want to find sales by continent.

The first country, Argentina, is in South America.

Click in the cell containing the first country, then hold down the Ctrl key, click in the cells containing the other South American countries.

On the Pivot Table Analyze tab, in the Group group, click Group Selection.

Click where it says Group1 and type in South America for the group name.

Continue similarly for each set of countries.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Hampshire and Somerset.

 

Microsoft Excel – freezing top row in a Pivot Table

I was at client site recently and someone had a Pivot table with a lot of rows. It was therefore hard to see what the column headings were.

Just as when you are trying to freeze rows in any Excel data set, click in the row under the top row of the pivot table. Go to the View tab, then in the Window group, from the Freeze Panes dropdown, select Freeze Panes. That should do the trick.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Hampshire and Somerset.

Microsoft Excel – using Icon Sets to show whether figures have gone up, down or stayed the same

In the example above, I would like to use icon sets to show whether sales figures have increased, decreased or stayed static.

In cell D5, I created a formula =if(c5>b5,3,if(c5=b5,2,1)) – i.e. if the figure had increased, D5 becomes a 3, if it is static, it becomes a 2, and if the figure has decreased, it becomes a 1.

I then selected the numbers in column D, then on the Home tab, in the Styles group, I clicked the Conditional Formatting dropdown, clicked Icon Sets and selected the first of the Directional options.

Then from the Conditional Formatting dropdown, select Manage rules, then Edit rules and select Show icon only.

Click OK twice.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Hampshire and Somerset.

Microsoft Excel – Charts with missing values

If you create a line chart where there are blanks in the data, the resulting chart can look rather odd!

On the Chart Design tab, click Select Data.

From the Select Data Source dialog box, click Hidden and Empty Cells.

Choose from one of the options. Here, I have chosen Connect data points with line. Click OK. Your chart will no longer have gaps.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Hampshire and Somerset.

 

Microsoft Excel – The UNIQUE function

If you are an Excel 365 user, you will have the UNIQUE function, which looks at a range of cells and produces a list of unique values. This may, be useful, for example, if you wanted to create a Data Validation List from data that is already there.

In the example below, I have a list of orders, but some customers have made more than one order and I just want a list of customer names.

In cell H2, I use the formula

=UNIQUE(B2:B154) which is the range that I am searching through.

I get a list of unique names in column H.

The syntax for the UNIQUE function is UNIQUE(Array, [by column], [occurs once]).

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Hampshire and Somerset.