Which features of Microsoft Excel do you use? Want to get up to speed quickly on the most useful ones? As you probably know, even at the most basic level Excel allows us to perform calculations (like a calculator) and manipulate text (like a word processor). Its power comes from being able to do multiple related calculations, based on its grid structure. So while with a calculator or on paper we can do simple sums, in Excel we can do the same sum many, many times.
Below are five features you should be using – if you aren't already. Learn everything about these tips: they will improve your spreadsheet skills and allow you to successfully organise your data to your satisfaction.
1) Pivot Tables
PivotTables summarise large amounts of Excel data from a database that is formatted where the first row contains headings and the other rows contain categories or values. The way the data is summarised is flexible but usually the Pivot Table will contain values summed over some or all of the categories.
If you’re new to creating PivotTables, Excel 2013 can analyze your data and recommend a PivotTable for you. Once you’re comfortable with PivotTables you can start from scratch and create your own.
To create a Pivot Table, make sure your data has column headings or table headers and that there are no blank rows. Click any cell in the range of cells or table.
INSERT > Tables > Recommended PivotTables
In the Recommended PivotTables dialogue box that launched, click any Pivot Table layout to get a preview then select the one that works best for you and click OK.
Excel will provide a selection of recommended PivotTables for your data. Excel then places the PivotTable on a new worksheet and shows the field list so that you can rearrange the data to best suit your needs. Again, make sure your data has column headings or table headers and no blank rows.
2) Conditional Formatting
Conditional formatting, as its name suggests, changes the format of a cell dependent on the content of the cell, or a range of cells, or another cell or cells in the workbook. Conditional formatting helps users to quickly focus on important aspects of a spreadsheet or to highlight errors and to identify important patterns in data.
Conditional formats can apply basic font and cell formatting such as number format, font colour and other font attributes, cell borders and cell fill colour. In addition, there is a range of graphical conditional formats that helps with visualising data by using icon sets, colour scales, or data bars.
The chosen conditional format is applied to a cell based on a condition you set or a condition that Excel generates by comparing the values of cells in a range. So, for example, in a list of staff salaries, a conditional format could be applied to any salaries greater than a certain amount, any employees who joined before a specific date, or any employees with a specific name. The graphical conditional formats would be applied to the column of salaries and would, by default, be based on an analysis of the highest and lowest values in the list but this can be overridden if required.
Conditional formats can be applied very simply and quickly just to highlight certain cells or can be used in much more complicated and imaginative ways to show values graphically or automate the formatting of a spreadsheet.
3) Sorting and Filtering
Excel spreadsheets help us make sense of large amounts of data. To make it easier to find what you need, you can reorder the data or pick out just the data you need, based on parameters you set within Excel. Sorting and filtering your data will save you time and make your spreadsheet more effective.
Suppose you have a list of hundreds of records including dates, ages, names, cities, and more. You can quickly organize the data to best suit your needs using Excel’s sort and filter features.
When you sort information in a worksheet, you can quickly organize the data and find values quickly. You can sort an entire worksheet or a range or table of data. Sorting can be done by one or more columns.
Let’s suppose you are a teacher and your class recently scored the following on a test:
You could sort on first name (alphabetically), second name (alphabetically) or by the test score (numerically). In any of these cases, you would want the entire row to move together, so Jane Smith doesn’t become Jane Mawer. Let’s say you want to sort by the test scores, highest to lowest.
4) Basic Math
At the heart of any Excel spreadsheet are the numbers within the data. Using basic math functions to manipulate those numbers is one of the features that makes Excel so powerful.
Simple calculations can be entered into the formula bar in Excel just as they would be written on paper. As with all formulae in Excel, start a calculation with the = sign.
You can type the calculation you want to perform directly into the cell or the formula bar and when you press Enter the answer will show in the cell.
Another option is to use multiple cells to construct the formulae, as we see here (where cell A1 (or 87) plus cell A2 (or 16) equals cell A3 (or 103)):
In order to perform the basic mathematical operations such as addition, subtraction, multiplication, or division to produce numeric results we use the following arithmetic operators:
+ (plus sign) for addition
- (minus sign) for subtraction
* (asterisk) for multiplication
/ (forward slash) for division
Excel interprets the = (equals) sign as indicating a calculation is to be performed and calculates according to the operators indicated from left to right.
For more complicated math calculations, check out our Working with Numbers Chapter.
5) Mixed Type Charts
Mixed type or combo (combination) charts combine two styles of charts, such as Excel’s column chart and line chart. This format can be helpful for displaying two different types of information or a range of values that varies greatly.
For example, we can use a column chart to show the number of homes sold between June and December and a line chart to make it easier to identify the average sale price by month. To create this chart, highlight all the data and select the Insert Combo Chart option in the Charts group of the INSERT ribbon tab:
Microsoft Excel is arguably the most complicated program out of the Microsoft Office family, but hopefully with the help of these key features (and our unique filter) you should be one step closer to becoming an Excel guru in the workplace or at home.
No comments:
Post a Comment