Guests

Excel provides a feature that is intended to split a single column of data into multiple columns. For instance, if you had a column of full names (first and last) and needed to separate these out into two columns: 

Pivot Tables

Introduction to pivot tables

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 - Drop-down lists

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. 

Vlookup & HLookup

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. 

Syntax

=Vlookup(Value, Table, Col_index, [Range_lookup])

Arguments

Value - The Value to look for in the first column of the table

Adding Chart Elements

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.

Charts and Graphs

Create a chart

1. Select the data for which you want to create a chart.

IF functions

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