As both an engineer and a marketer I have found myself working a great deal in and with Microsoft Excel. Over time I have learned many tricks and workflows that are useful, some of which have saved me hours. The best resource for picking up new tricks has been friends and colleagues because they have likely encountered a similar problem. For the more in depth item the help menus and internet searches have proved invaluable. Here is a series of tricks I have found useful, hopefully writing them down in one place will help me remember them when the time is necessary, if not at least there is one place to look.
- Paste As Value– When transferring data from sheet to sheet and wanting to grab formula result rather than the actual formula.
- Find Duplicates – Often in lists finding a duplicate value can avoid headaches down the line
- What-If Goal Seek – Use Excel to get the answer you want to various scenarios “How many units do we need to sell to justify this marketing program?”
- Record a Macro for Any repetitive process – When you realize a correction needs to be made to many entries in a data set, recording a quick macro can avoid a whole day spent doing data entry
- Highlight alternate rows in chart – Starring at reports can become mind numbing and dizzying, at least proper formatting can help organize things.
- Keyboard Shortcut Anything with Alt Key
- Alt key has two great functions. First you can hit Alt to get an immediate shortcut key for anything on the menu bar.
- When looking at a menu an underlined letter means “Alt” + Letter will select that item
- Keep a spreadsheet looking pretty with IFERROR – Avoid having the dreaded #DIV/0 show up all over a pretty spreadsheet and generally avoid having things break down by at discarding any mistakes (Warning:This can lead to it’s own set of problems if not checked)
- Easy Screenshots – Built in tools make it easy to add various images on the fly and avoid having to open any additional programs
- Compress Images – Make a file smaller to be able to email it!
- Alt+Enter to make multi-line text in a single box – Sometimes you need two lines of text in a cell but the spacebar will not get you there.
- Copy and Paste only Visible Cells: – Want to share or copy only part of a spreadsheet, this will make things much easier.