Microsoft Excel - 8 top tips
Microsoft Excel is a brilliant tool for showing, creating, and enhancing the way data is used. It can be used in multiple formats and can be used in a variety of ways, here are the 8 top tips I wish I knew 5 years ago!
Number formats
Table
Filtering
Total column
Slicer
Data Validation
Conditional Format
Calculations
Watch the video here
View the PDF format
NUMBER FORMATS
Number formats in Excel not only change the layout of the data and improve the visual elements, but they can be vital when linking multiple datasets together and creating calculations. To change a number format select (highlight) the cells you wish to change and choose a field from the option in Home > Number
You can also change the currency field, make the cell a percentage, add a comma, and change the number of decimal places underneath the dropdown. Image below.
Finished data:
TABLE
Converting a data range into a Table has a vast amount of benefits, including; when data is added to the end of the column it will expand the table, named ranges when creating calculations, total row automatically built-in, slicer option, and secure the data in one data range so calculations will always pick up and added rows and columns automatically.
To set a data range into a Table, simply highlight the whole range (including headers) and go to Insert > Table to choose whether or not your data range contains column headers or if Excel should create them.
Tip: you can select CTRL + T after highlighting your data range to convert it to a table.
Table Design
Within this new section, there are many different options, some of which are covered in this document. Make note of the ‘resize table’ button on the far left and the table name if you wish to link multiple tables together.
This format can easily be undone or changed if required, to undo the Table format go to Table Design > Convert to Range and this will reset your data.
FILTERING
You can filter any data range by highlighting your column and selecting Home > Sort & Filter. However, this is best done in a table to when the data is filtered, the corresponding information filters with it. Once you have converted your data range to a table you will notice the filter buttons displayed in the column headers (this can be removed in the Table Design tab). After selecting the filter, there are multiple options;
- Sort Smallest to Largest / Largest to Smallest – will sort the entire data set
- Sort by the colour – will sort the entire data set on a defined colour block/text already inserted
- Filters – won’t change the data position, but will put all of the defined ranges together.
- Selected filter – choose what type of data range you would like to see or exclude from the range.
Sort = changing the position of the cells to the defined range
Filter = showing a different view of the data range, the cells remain in the same cell location
To see if your data set is filtered or sorted, look at the row numbers. If they are highlighted blue and show minimised sections this has been filtered, this is showing you where the data is being pulled from.
TOTAL ROW
Within a table range, you can choose to include an automatic total row. Go to Table Design and toggle ‘Total Row’ this will populate a row at the bottom of the Table range. From here, you can toggle what you would like calculated, and once you filter the range the total row will filter also.
SLICER
Within a table range, you can insert a slicer that will allow the user to filter using pre-set buttons. Go to Table Design > Insert Slicer, and choose the columns you wish to include. Once created, you can toggle these buttons to show certain data sets. These can be layered with multiple options chosen.
DATA VALIDATION
When entering data into a spreadsheet, any combination of letters and numbers can be inserted. However, if you would like to specify only certain numbers, dates, or text in these cells this can be done using data validation. The benefit of this is if you have calculations or a slicer in place and one of the fields is spelled wrong due to human error, then that row wouldn’t be included in the calculation total or the slicer would add another option. See the example below.
To add data validation, highlight the cells and choose Data > Data validation. Choose the criteria option:
- Whole number – will not allow decimals or text
- Decimals – will always produce a decimal figure
- List – user can only select/type from a predetermined list
- Date – user can only type a date within a set date range
- Time – user can only type a time within a set time range
- Text length – text can only contain a certain amount of characters
You can also choose the error message when the user inserts the wrong information. By default, it will show the below message and will not allow the user to continue.
If ‘list’ is selected, it will open a toggle button and will show the user the available options.
Tip: another great way of using this function is in an automatic worksheet. See the below example, you could make the top cell the picker field and the cells coloured in grey would have calculations based on the determined selection. Therefore, once the user changes the ‘Student Name’ field it will change the data in the corresponding grey cells.
CONDITIONAL FORMAT
To make certain figures and numbers stand out, you can condition certain cells to show a variety of formats depending on a variable. For example, the below table includes two formats – ‘Sale’ column has data bars to show the scale of sales, and secondly, the highlighted values in the ‘Percentage’ column when the number is greater than 100% should highlight green.
To add a quick conditional format, highlight the cell(s) you wish to format and choose Home > Conditional Formatting.
From here, there are many different options:
- Highlight cells Rules – cells change after a specific rule
- Top/Bottom Rules – cells change depending on their value if top/bottom in the range
- Data bars & Colour scales – cells will show the top/bottom figures in a colour scale within the defined range
- Icon sets – cells will display an icon dependent on the variable.
For more options, to layer multiple conditions, and more advanced functions click ‘manage rules’ to view all rules created and to update them. By double-clicking on a rule you will see the more advanced format with more options.
Manage rule view:
Edit/New rule view:
Here you can change the entire rule and even change the format of the cell change in ‘Format’.
To clear a rule you can either choose ‘Clear Rules’ in the dropdown option or select ‘Delete Rule’ within the ‘Manage Rule’ window.
CALCULATIONS
There are a vast majority of calculations within Excel that do a variety of different things. They can be endlessly built on each other or very simplistic, depending on the user preference. Calculations in excel essentially take existing data and populate a calculated number from it, pre-determined by the user. Examples below:
Calculating the percentage:
Excel has built-in information and helps when building the formula, not only when typing it out. Go to the Formula tab, from here you can see built-in help from Excel.
Example: Calculate the average sales and total sales from the table data set using the ‘Sale’ and ‘Staff code’ columns
Data set:
Calculations to go into:
The calculation will use ‘AVERAGEIF’ once typed out into Excel, it guides you on how the formula needs to be designed.
AVERAGEIF( range , criteria , [average_range])
We need to use a matching factor to bring the data across, in this example, we will be using the ‘Staff code’ column to match both data sets together. Therefore we can replace the values:
Range is Table column contents (Staff code column in Table)
Criteria are what value we want to match (individual staff code)
Average_range is the range we want to average
AVERAGEIF ( staff code column, staff code cell to match, [average ‘sale’ column])
Finished calculation:
=AVERAGEIF (Table2[staff code],‘Ex2 Chart’!A7,Table2[Sale])
Once the calculation is complete, click ‘Enter’ on your keyboard and drag the formula down by putting your cursor to the bottom right of the cell and click and drag once the black ‘+’ appears. You’ll notice the formula fills in for the corresponding codes.
To complete the total sales, the same formula can be applied but by using ‘SUMIF’ in replace of ‘AVERAGEIF’.
BONUS: CHARTS AND TABLES
Within Excel, you can quickly showcase data in a chart and this can be fully customisable. To do this highlight the data and go to Insert > Charts and select the chart style you would like to use.
Once created two tabs appear ‘Chart Design’ and ‘Format’ which allows you to change the chart design and format.
By clicking on the chart, you can quickly change the layout and design of the chart using the button that appear on the right of the chart.
SUPPORT
If you would like any further information regarding the training documents, please contact Alice Matthews, System and Technology Trainer at Stamford Endowed Schools at ARMatthews@ses.lincs.sch.uk.