I am a big-time Excel user. And I thought I had a pretty good understanding of most of its features. So I can't believe I didn't discover this one until I prepared Part 8 of the Excel Boot Camp series.
It relates to the full capabilities of a feature called Flash Fill.
I am a frequent user of the basic Flash Fill capabilities - where you can enter, say, enter a month in one cell and drag down (or across) across other cells to fill those cells each of the following months in sequence. Or you can enter a date in a cell, and drag down (or across) the next lot of cells to get the dates in sequence. Same for sequence numbers.
But I didn't know the scope of the other automatic filling that Flash Fill could do - and they are such time-savers.
Members of the ITandCoffee Club can find out more about these features in an extract from the video of Part 8 of our Excel Boot Camp series - select the below button to access this video.
Interested in learning more about Excel?
We will be regularly running the Excel Boot Camp series of classes. Any upcoming sessions are shown below. If there are none scheduled at the moment, please register your interest to be notified when the next series is available - here is the link.
Watch the videos
If you would rather learn about Excel in your own time and at your own pace, we have the videos of the Boot Camp series available - purchase access to these videos for 120 days.
Microsoft Excel Bootcamp - Access to 8-Part Video Series (120 days)
Purchase 120 days' access to watch all 9 parts the Microsoft Excel Boot Camp series of videos. These 9 parts are being added each week over the period of 8/8/20 to 10/10/20 (excluding 28/8/20). (Note that the access period will become 90 days for purchases made after 10/10/20.)
Here's what's covered in each part:
Week 1 – Getting Started
In this first session of our Microsoft Excel Boot Camp, we start at the very beginning – creating a new Excel workbook and understanding Excel terminology – Workbook, Worksheet, Cell, Rows, Columns, Formulae. We take a guided tour of the main areas of the Excel app (the Ribbons); we look how to easily ‘zoom ‘your document to a size that suits your eyes; saving your document; re-opening your Excel file; and understanding where OneDrive fits in to the picture (and whether to save your Excel files there). We also look at the Templates that you can use to get started on your spreadsheet.
Week 2 – Creating a customer list with some simple formatting and formulae
In this second session, we use the example of a customer list, looking at: entering some simple data into your list; re-sizing columns and rows (manually and using ‘autofit’); text wrapping, entering different types of data into cells (eg text, integers, numbers, dates, dollars); how to select one or more cells (or whole rows / columns); formatting cells (size, bold, colour, background colour, number format, date format); selecting rows, columns and cells; inserting and deleting columns and rows. We then look at some basic formulae for adding the content of rows and columns, and how to copy formulae and formatting across the multiple cells.
Week 3 – Creating a standard invoice with calculations for GST and totals
In Part 3, we look at the example of a standard invoice: how to format this invoice as an A4 sized page to include a business logo and business details; how to set up your invoice for entry of the items you wish to charge for; setting rows and columns to automatically calculate GST and totals; hiding the grid lines in printed version; including borders around required areas; turning the invoice into a Template that you can use over and over and how to access that template when you need it; setting up your worksheet so that it fits to a single page width; converting that invoice into a PDF that you can send via email and/or save somewhere on your computer/device.
Week 4 – Sorting, filtering, totals/sub-totals and finding/removing duplicates
Part 4 looks at working with data that needs sorting, sub-totalling, grouping or that has duplicates that need to be removed. We also look at the wonderful ‘filter’ options available, to allow you to more easily find the data you are looking for in what may be a very long list of rows – based on whatever criteria you specify.
Week 5 – Basic & Useful formulae PLUS data validation
In part 5, we focus in this part on using Formulae in your workbook – looking at some of the more common formulae that relate to data in a single worksheet, and across multiple worksheets. We also look at how to set up data validations, which can help with data entry and consistency – setting up a ‘pick list’ of values and how this can ensure entered data is limited to this list.
Week 6 – Importing/exporting CSV files, working with sheets, conditional formatting
In Part 6, we look at importing a ‘comma-delimited’ file (a file in text format, with commas – or some other ‘delimiter’ separating the fields) into Excel format, and at how to create a CSV file from a sheet in your Excel workbook. We look at working with multiple sheets – adding, copying, renaming, moving, creating another workbook from a sheet, hiding, and protecting sheets. We also look at setting up conditional formatting that will automatically bold, colour, underline (or any other format) cells, columns and rows in your sheet.
Week 7 – Summarising and Reporting your data - Pivot Tables and Charts
Part 7 looks at how utilise the Pivot Table feature to see your data represented in a summarised format, counts, totals, averages and other functions reflected in this summarised format. We also look at presenting your data in charts that provide a wonderful visual representation of what is otherwise a flood of text and numbers!
Week 8 – All sorts of handy tips, shortcuts and formulae
In Part 8, we look at really handy shortcuts, features, formulae and tips – to help you use Excel efficiently and effectively. This session will capitalise and expand on what we have covered in earlier sessions, quick ways of achieving things that require several keystrokes and use of the mouse abd discovering great formulae that you may not realised are available.
What's on at iTandCoffee ?
Join us for a short, fun 'topic of the day' classes known as PTT sessions (Personal Training for your Technology fitness!) - these are run on a regular basis. Or join any of our other classes shown below to learn so much about your technology.
If you have questions, why not join our The iTandCoffee Club to attend fun and informative 'user group' meetings.
All classes are run as online classes (using Zoom) which means you can attend from anywhere.
Call 1300 885 420
to book an appointment or class, or to enquire about our services and products
SENIORS CARD WELCOME HERE:
Seniors Card holders qualify for a 10% discount on all classes booked and paid for online (excludes PTT sessions and classes already discounted during COVID-19 crisis). To activate the discount at the time of booking, select Redeem Coupon or Gift Certificate and enter 10OFFSEN before selecting Pay Now.