PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.
This blog post explores 5 advanced PivotTable techniques.
- Grouping fields by month and year
- Calculating data as a percentage of the total
- Using Slicers
- Applying Conditional Formatting to PivotTable data
- Creating calculated fields
Add drop lines to a line graph in Excel to connect the data point to its label on the axis. This extra visual chart element can make it easier to view the data on busy charts.
Take the line graph below for instance that shows the call volume for 14 hours of a day. It can be awkward on this chart to quickly view the time of day for a specific data point.
This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of a workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.
By creating a macro this seemingly repetitive task can be performed at the click of a button. Continue reading
Custom Fields in Microsoft Project allow you to define your own fields (columns) for data that meets your organisation needs.
By creating your own custom fields you can display information on the project plan that is tailored to your requirements.
This tutorial looks at 5 reasons you may want to set up custom fields in Microsoft Project including creating drop down lists and using formulas in fields.
- Create a drop down list
- Display multiple fields next to task bars in Gantt Chart
- Sum the total duration of tasks
- Change colour of task bars dependent upon conditions
- Using graphical indicators in custom fields
If you are sharing a PowerPoint presentation with others, you may decide to embed fonts in the PowerPoint file. This will ensure that others can use those fonts, even if they are not installed on their machine.
If you have used a custom font that another user does not have, it will be replaced by the default font on their machine. The presentation will then have an inconsistent look to it.
Be aware that embedding fonts in a PowerPoint file will increase its file size. However this is typically a small price to pay for a consistent and reliable presentation. Continue reading
If you have ever tried to add a hyperlink to a userform, you would have noticed that there is no hyperlink button on the toolbox.
You may not be able to insert a hyperlink like you would on an Excel spreadsheet, but you can create a link by inserting a label and applying the OnClick event.
Adding a Hyperlink to a Userform
In this example we will add a hyperlink so users may email an enquiries department from the userform. Continue reading
When using a PivotTable to summarise a large list of data, you may need to count unique occurrences in a particular field.
For example, you may have a list of data like the one below and want to count the number of different customers in each country, or the number of different products that were sold in each country.
If you added the Customer Name field to the Values area of a PivotTable and summarised it with a Count you would get the result below.
However this is counting all of the transactions in each country. We need to count the unique customers making those transactions in each country.
In this blog post we look at how to create a macro that will look in a list and format all the instances of specific words that appear in a cell.
This macro is case sensitive and will also only apply the formatting if it is the whole word. For example, if you are formatting all instances of the word ‘red’, this macro will not format it if used in words like ‘reddish’ and ‘sacred’.
Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.
This article looks at 4 advanced techniques for summing values including;
- Summing a range that contains errors
- Creating a cumulative total
- A consolidated sum from multiple sheets
- Summing Every Nth Value in a Range
When performing a mail merge from Excel to Word, your mail merge fields tend to lose their Excel formatting. This is especially common with date, time and currency fields.
This tutorial will look at how you can correctly format date, time and currency mail merge fields from Excel. Once the formatting is applied to the mail merge document it will be remembered for future use.