Excel Beginners

Navigating Excel: Step 1 To Mastering Spreadsheets

Home is where the tools are

The Home Tab

Clipboard Tools

Paste and Paste Special

Paste allows you to take content previous selected and copied/cut and place it in a new location.
Paste Special allows you to choose which aspects of the copied/cut information you would like to move to a new location.
- This could be the values in the cell, the font and formatting of the cell, the formula contained in the cell, or one of many other options.

Cut

Cut allows you to take and remove the set of cells you have selected and place them in a new location.

Copy and Copy as Picture

Copy allows you to take a copy of the cells you have selected, keeping the original cells intact, and place them in a new location.
Copy as Picture allows you to take a range of cells and create an image box containing their information at the time the picture was created. It will not track future changes to the cell.

Format Painter

Format Painter allows you to copy the text and cell formatting of your source range, onto a new range. Double clicking the Format Painter tool will allow you to paste the formatting to multiple ranges without needing to re-select the tool or cells.

Font Tools

Font and Font Size

Font allows you to change the text style of the selected cells to any of the font styles in the drop-down list
Font Size allows you to change the text size of the selected cells to any of the values in the drop-down list, you can also type in your own number for further precision.

Increase and Decrease Font Size

Increase Font Size allows you to increase the size of the font in the selected cell by one level, as they appear in the Font Size drop-down.
Decrease Font Size allows you to decrease the size of the font in the selected cell by one level, as they appear in the Font Size drop-down.

Bold / Italic / Underline

Bold adds an additional thickness to each character.
Italic adds a small right leaning slant to each character.
Underline adds either a single line below each character, or a double line under each character.

Borders

Borders allows you to format the edges and intersections between cells in the range you have selected. You have the option to format any combination of these edges as you see fit.

Fill Colour and Font Colour

Fill Colour allows you to change the background colour of the cells you currently have selected.
Font Colour allows you to change the colour of the displayed text of the cells you currently have selected.

Alignment Tools

Vertical and Horizontal Align

Horizontal Align is split into three buttons – top align, middle align, bottom align – which will shift the positioning of the values in a cell unless another formatting rule overrides your selection.
Vertical Align is split into three buttons – left align, center align, right align – which will shift the positioning of the values in a cell unless another formatting rule overrides your selection.

Orientation

Orientation allows you to have the values within a field displayed at the angle of your choosing. There are five preset options for you, as well as the option to bring up a full menu with more customisation.

Increase and Decrease Indent

Decrease Indent removes one level of indentation from the selected region of cells
Increase Indent adds one level of indentation to the selected region of cells

Wrap Text

Wrap Text formats the selected cells so that if the string or result from a formula is too long for the width of the cell, it will automatically increase the height of the row it is in to accommodate the text over multiple lines. It will not modify the width of the cell automatically.

Merge and Unmerge

Merge allows you to join multiple cells over any number of rows or columns into one larger cell. Only the first value found (starting from the upper left corner of the range) will be kept, the others will be discarded.
- There are multiple types of merges that can be performed using the drop-down menu button next to the merge cells icon.
Unmerge allows you to split apart all cells in the selected range back into their individual cells. The value from the merged cell will be placed in the cell in the upper left corner of the range of each cell which is being split apart.

Number Tools

Number format

Number format allows you to format the selected range of cells into a predefined format. The formats cover the most common formats used; custom formats can also be created.

Quick format

Quick format allows you to easily access three of the most common predefined formats available in Excel: Dollar/Accounting format, Percentage format, and general number format.

Increase and Decrease Decimal Places

Decrease Decimal Places allows you to limit the number of decimal places that are visible in the selected range, the additional decimal places are still saved in the cell and are used in calculations but will not be displayed to the end user.
Increase Decimal Places allows you to extend the number of decimal places that are visible in the selected range, the additional decimal places will always be displayed, even if it will result in trailing zeros.

Styles Tools

Conditional Formatting

Conditional formatting allows you to change the formatting of a selected range of cells based on the criteria of your choosing.
- This could be based on the cells being the highest or lowest values in the range, between specific percentiles, contain specific text or date values, or you can create more complex formulas yourself.

Format as Table

Format as Table changes the existing range into a table format, this creates header rows for your data, and creates dynamic ranges for your formulas. It is a powerful tool to use under the right circumstances.

Cell Styles Formatting

Cell Styles Formatting allows you to choose from a preset list of style choices and allows you to apply that formatting to the selected range of cells. These formats are often used to accentuate key areas in reports.

Cells Tools

Insert Cells

Insert Cells allows you to perform four functions within a workbook
- Insert individual cells within a worksheet, pushing existing cells down or to the right based on their placement
- Insert entire rows within a worksheet, pushing existing cells down a row
- Insert entire columns within a worksheet, pushing existing cells to the right
- Insert a new worksheet to the workbook, giving it a default name

Delete Cells

Delete Cells allows you to perform four functions within a workbook
- Delete individual cells within a worksheet, shifting existing cells up or to the left to fill its place
- Delete entire rows within a worksheet, pushing existing cells up a row
- Delete entire columns within a worksheet, pushing existing cells to the left
- Delete an existing worksheet from the workbook

Format Cells

Format Cells allows you to perform a multitude of formatting actions, the main ones are as follows
- Modify or auto adjust the height and width of a set of cells
- Hide or unhide a set of rows or columns
- Rename, hide or unhide worksheets within the workbook
- Add sheet or cell protections including passwords to prevent modifications

Editing Tools

Auto Sum tools

Auto Sum allows you to quickly insert a sum, average, or count of a relevant range of data. It does allow you to insert more than just those three functions, but you will need to open the advanced menu to do so.

Fill tools

Fill allows you to fill in a series of cells with the same formula or values as in your selected range. You can select which cells are filled with this data (up, down, left, or right) from the selected range.

Clear tools

Clear allows you to remove the content, formatting, comments, notes, and hyperlinks from the selected range of cells which are selected. There are four options allowing you to remove these different types of content with greater control.

Sort and Filter tools

Sort and Filter allows you to sort your selected data in ascending or descending order, as well as to define your own custom sort list. It allows you to apply filters to your data in terms of whether it contains or does not contain specific values, if it is greater or less than specified numbers, or even by colour should your data be formatted with different text or fill colours.

Find and Select tools

Find and Select allows you to search for specific occurrences within your worksheet or workbook.
- This could be text strings, number strings, formulas, notes, conditional formatting, or data validation rules.
- You also have access to the Go To and Go To Special options, which can either select or navigate you to cells which match the selected criteria.