Pivot Tables are covered in detail on our Advanced Excel Training course. However, for some basic pivot table tips, read How to Create a Pivot Table and How to Change the Function of a Pivot Table. If pivot tables are a bit advanced for you and you’re looking to start afresh then our beginners Excel Training might be perfect for you. Click the link to read the full course description.
Pivot Table Slicers Tutorial
In a Pivot Table you can apply filters to your data, using the report filter and by filtering within the row or column fields. When using the multiple items in a report filter it is not obvious which criteria you have specified and likewise with the row and column filters.
So what can you do? The answer is to use Slicers in Excel 2010 and this is how to do it: Read the rest of “Excel 2010 Tutorial – Pivot Table Slicers”
It doesn’t take long for what was a simple spreadsheet to grow and become a monstrous maze of data. These become increasingly difficult to navigate and work with, making the simplest tasks take an unnecessary amount of time.
While it’s no substitute to proper Excel Training, Microsoft included several handy shortcuts and little-used functions that once used become essential for future spreadsheet set up, manipulation and navigation.
If you find that you are spending most of your time performing the same monotonous spreadsheet tasks, such as copying data from one place to another, you need to look into is setting up your own macros.
Name your Macro and set its shortcut once you have pressed the 'Record Macro' button.
Built using VBA (Visual Basic for Applications), a Microsoft built event-driven programming language; macros allow the user to create a set of instructions which it can complete within the parameters you set. This means that you can set them up to automatically perform tasks such as cell formatting, cell selection, adding or removing rows or columns or even adding a date into your spreadsheet. Read the rest of “Time saving tips for Excel”
Our last tutorial taught you how to use conditional formatting on Microsoft Excel 2007 & 2010 to colour code data. This week we look at another use for conditional formatting – highlighting duplicate and unique values within a spreadsheet.
We’ve created a basic table of data of a pretend survey in which children were asked their shoe size. This tutorial shows you how to use conditional formatting to eliminate multiple entries and also to find unique values. If the data is entered manually these tips are great for making sure there are no mistakes with the data.
1. Eliminate accidental entries
For the purposes of the tutorial the data is limited and so it is easy to see duplicate/unique content by eye. However, if the data was for a whole school year of 300 pupils then conditional formatting becomes highly useful.
Firstly we are going to check to see if there are any multiple entries. Select the data you want checked and click Conditional Formatting > Highlight Cells Rule > Duplicate Values. Read the rest of “Excel Tutorial – Finding Duplicate Values & Unique Values using Conditional Formatting”