Pivot tables can be used for many things however their best function is served by collating data for you. This is easier to create when the data is presented in log format. What we mean by this is that you should log each transaction individually and not auto-sum or total anything as it will be done by the Pivot table for us.
an example of a log entry with sales data could be:
Excel has a feature that allows you to format a cell so that it may only contain certain answers you have specified. These will be displayed as a drop-down list that displays when a cell with the formatting on is selected. This is useful for removing things such as spelling errors or typos and allows less chance for error whilst inputting data.
There are multiple ways of highlighting or removing duplicates from an Excel worksheet.
Excel can use a function to lookup and retrieve data from a specific column or row within a table. Vlookup works to return back a column of data and Hlookup works to return a row of data instead, therefore the V stands for vertical lookup and the H, Horizontal lookup.
=Vlookup(Value, Table, Col_index, [Range_lookup])
Value - The Value to look for in the first column of the table
When creating charts/graphs in Excel you may find that some labels are not added by default and must be added manually or are in an unsuitable place. Excel also allows for modification of these labels, as well as their placement. In this example i'm going to amend a pie chart. If you are not sure how to create a pie chart, you can find a guide on chart creation here.
Create a chart
Select the data for which you want to create a chart.
If functions are used when we want to specify a criteria to limit our function to. For instance, if we were interested in values above a certain amount we could use =countIf in order to count the number of the values in a range, that are above a certain value. There are multiple different functions that can use IF's. These include:
=SumIF
=CountIF
=averageIF
A sparkline is a small graphic designed to give a quick representation of numerical or statistical information within a piece of text, taking the form of a graph without axes. This is displayed within a single cell and can be used to visualise data when you don't have enough room or it isn't suitable for a full size chart.
Conditional formatting is a feature that has a variety of uses. Mainly surrounding different ways of displaying data corresponding to the value found within the cell or range of data. This can help you visualise the data faster than reading off the values using colour schemes and icon sets to show the quantities or differences between values more effectively than numbers would at a glance.
The fill handle can be used in a variety of ways that may save you time. From repeating sequences or repeating data to repeating a formula over a large area.
The fill handle is the name given to the green dot found on the bottom right corner of the selected cell/s.
