![]() ![]() ![]() Use colours consistently to indicate the type of cell. ![]() This makes things transparent and means to can change the number without having to edit a formula, or perhaps a bunch of formulas. Put it in a cell and refer to that cell in the formula. Never put a number directly into a formula. I generally put them on separate sheets within the same workbook. Just a few key tips.Īs far as possible, keep your data entry cells separate from your calculation cells that contain formulas. File | Options | Advanced | uncheck the option “Enable fill handle and cell drag-and-drop”. Personally, I dislike this too, as I’ve sometimes done it by accident, so I switch it off too. File | Options | Advanced | uncheck the option “After pressing Enter, move selection”.Īs an alternative to cut-and-paste, Excel has an option to “Drag and Drop” a cell. I often want to move in some other direction, or not move at all, so I switch off that default setting. Changing some default settingsīy default, when you type in a new value for a cell and press Enter, Excel moves the selected cell down. Vastly better than having to move the mouse pointer to the selected range, right click, look for Copy somewhere in the pop-up menu and click on it.Īlt-E Alt-E: find and replace. Sure you can use the built-in file-recovery system if something goes wrong, but it is safer and less mucking around if your main copy of the file is up to date.Ĭtrl-C: copy the currently selected range. I do this so frequently (every few minutes) that it has become instinctive, and I don’t even think about it. It’s one of a number of reasons I prefer Windows.Ĭtrl-S (hold down Ctrl and hit S): this saves your file. I used to use a Mac, and the system of keyboard shortcuts it uses are far less effective and consistent than they are on a Windows PC. I have memorised a modest number of keyboard shortcuts and they mean I can work more quickly than I could if I relied on the mouse. Using the keyboard is more efficient than using the mouse for many tasks. You can combine the previous two tips: hold down Shift, hit End, and hit an arrow key, and you select all cells to the edge of the table. Excel selects a rectangle of cells consisting of where you started to where you are now. You do this by holding down the shift key and then using navigation keys (up, down, left, right, PgUp, PgDn, Home) to move around. It only works fully if the table it full of numbers (no empty cells except outside the table).Įven more useful is selecting cells using the keyboard rather than the mouse. This is especially useful when navigating large tables (e.g., with 50 or 100 columns or rows of numbers). This sends you in that direction until it runs out of cells with any content. If you are sitting in a table or a list of numbers, you can jump to the top, the bottom, the extreme left of the extreme right of the table but hitting the End key (don’t hold it down) and then hitting the arrow key for the direction you want to go. Most people navigate around Excel using the mouse, but I find that using the keyboard is usually more efficient in some situations. Using Excel so extensively, I have accumulated some knowledge that might be worth sharing, so here is a bit of a grab bag of tips and tricks. I’m not a super-user but I’m better acquainted with it than most. There are things you need specialised tools for, of course (e.g., specialised statistics, large optimisation models) but even in those areas, Excel does a good job of solving many practical problems. It is my go-to tool for all things quantitative. I use it every day for a huge range of tasks. There are some freebie spreadsheets available, which can be good, but they can’t match Excel. Microsoft Excel took over as market leader in 1995 and has been dominant ever since. Then the power and ease of use was stepped up a couple of notches in Lotus 1-2-3 in 1983, which quickly led the market. First came VisiCalc and then SuperCalc, but the first one I learned (on a CP/M computer, the operating system that predated MS-DOS, let alone windows) was the relatively obscure PerfectCalc. The flexibility, the range of features and the ease of use mean that there is some aspect of your work that you should be using a spreadsheet for.Ī basic type of spreadsheet program existed on mainframe computers as far back as the 1960s, and then when micro-computers were invented, spreadsheets quickly became one of their two essential programs. I still meet people who say things like, “I just use R, I have no need of spreadsheets”, but they have no idea what they are missing. If you do any sort of work with numbers, you should be using a spreadsheet. Here I share a few tips on using Excel efficiently and I highlight a few features that you might not know about. ![]()
0 Comments
Leave a Reply. |