Improve your Excel confidence and skills
There’s no shortage of data being collected by business software and systems but knowing how to pump out useful information using that data is where most of us are lacking.
Whether you like it or hate it, Microsoft Excel is both powerful and ubiquitous. Almost every software system you’re using will produce reports that open in Excel, so there’s great value in developing some confidence and skills in doing some basic analysis in it.
Excel tips & tricks
To get you started, here’s some of our best features and tips for novice Excel users that will make a big difference if you weren’t already aware of them. If you’re not sure what any of these features are or do, download our demonstration excel spreadsheet, or give us a call!
- Freezing panes – keep an area of a worksheet locked and visible while you scroll left and down. Essential for seeing the column and row headings that cells belong to. Select the cell above and left of which you’d like to freeze > View > Freeze Panes
- Autofilter – select your full data set and click Data > Filter to turn on autofilter. Use the drop down buttons at the top of each column to reveal lots of simple sorting and filtering options
- Adding text in a cell – when typing text in cells, use “Alt + Enter” to insert a return and new line. Turn on or off text wrapping as needed: Select cells > right click > format cells > alignment > toggle on off “Wrap Text”. Use the “ – “ key instead of bullets, and insert a ‘ before the first which will be hidden but tells excel it’s not a formula.
- Formulas with dates - Excel can do maths on dates as well. It can calculate the days (or working days) between two dates, convert dates into day, week or months of the year (which can help with say analysing monthly trends) and even calculate your exact age in days. See examples on the Dates tab in sample Excel file for download.
- Formulas with text – really helpful when working with contact or inventory lists, you can perform lots of bulk actions with text using formulas. Examples shown in the sample file include removing, adding, joining or replacing sections of text.
- Conditional Formatting – shade cells or colour text depending on their value. Show great sales results in green, low bank balances red, completed tasks grey. There are lots of possibilities, see some examples in our sample spreadsheet.
Download our demonstration spreadsheet to see examples of these features in Excel.
If that’s all too hard and you want to outsource your business modelling and analysis talk to us today.
Other Excel resources we like:
- My Online Training Hub offers great programmed courses in Excel
- Lynda.com has many different courses. tutorials and tips in its excel area
- Contextures, a business offering various Excel add-ons and consulting has a comprehensive tips and tutorials page
- You can google just about any excel question and get a helpful answer, try for example:
- Excel workdays between two dates excluding public holidays
- Excel conditional formulas
- Excel what is a vlookup
- Excel generate random dates
- Excel how to calculate the mean, mode and median