Excel Hints & Tips - get extra mileage from Excel

Excel Hints & tips

GET EXTRA MILEAGE FROM EXCEL!

Please use the left and right arrows on the edge of each page to move through the presentation

CONTENTS

Sequential Dates

Freeze Panes

Comments & Notes

Flash Fill

Range Names

Customise the QAT

Summing Tips

Filling Data

Get in touch!

FREEZE PANES

To keep an area of a worksheet visible while you scroll to another area of the worksheet, go to the View tab, where you can Freeze Panes to lock specific rows and columns in place, or you can Split panes to create separate windows of the same worksheet.

Click on the image to access the Freeze Panes video

CONTENTS

COMMENTS & NOTES

You can add comments to cells. When a cell has a comment, an indicator appears in the corner of the cell. When you hover your cursor over the cell, the comment appears. And you can print your comments and notes too!

Click on the image to access the Comments & Notes video

CONTENTS

CUSTOMISE THE QAT

The Quick Access Toolbar is a customizable toolbar that contains a set of commands that are independent of the tab on the ribbon that is currently displayed. You can move the Quick Access Toolbar from one of the two possible locations, and you can add buttons that represent commands to the Quick Access Toolbar.

Click on the image to access the Customise the QAT video

CONTENTS

The video uses Word to illustrate how to customise the QAT but the method is exactly the same in Excel!

FILL DATA

Use the Auto Fill feature to fill cells with data that follows a pattern or are based on data in other cells.

Click on the image to access the Fill Data video

CONTENTS

Create sequential dates

You can quickly create a list of dates, in sequential order, by using the Fill Handle Fill handle or the Fill command.

Click on the image to access the Create Sequential Dates video

CONTENTS

flash fill

Use Flash Fill to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data, and works best when your data has some consistency.

Click on the image to access the Flash Fill video

CONTENTS

Range Names

By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Once you adopt the practice of using names in your workbook, you can easily update, audit, and manage these names.

Click on the image to access the Range Names in Formulas video

CONTENTS

summing tips

Quick Sum with the Status Bar

Quick Sum

If you want to quickly get the Sum of a range of cells, all you need to do is select the range and look in the lower right-hand side of the Excel window. This is the Status Bar, and it displays information regarding whatever you have selected, whether it's a single cell or multiple cells. If you right-click on the Status Bar a feature dialog box will pop out displaying all of the options you can select. Note that it also displays values for your selected range if you have those attributes checked.

You can use ALT+ = to quickly add the SUM function to a cell. Then all you need to do is select your range(s).

Quick Sum with Non-Contiguous Cells

You can quickly select multiple, non-contiguous ranges with Ctrl+Left Click. First, enter “=SUM(“, then select your different ranges and Excel will automatically add the comma separator between ranges for you. Press enter when you’re done.

CONTENTS

THANKS FOR WATCHING

WWW.BLUENOVATION.CO.UK

PLEASE GET IN TOUCH

CONTENTS