Mazars – welcome to our Digital Classrooms! We are opening our Digital Classrooms to individual registrations, making our world-leading financial...
When you are a graduate you will attend a lot of training, do e-learnings and be given “on the job” training, however I wanted to write a practical list of Excel tips and tricks that will help your every day use of Excel. Whether you’re an auditor, investment banking analyst or in financial management you’ll be using Excel, the below article should help you through your first few years. Even if you’re experienced using Excel some of the below tricks should help you out.
I wish I would have been told this on my first day at work instead of filling the cells with white, however, I ended up spending a fair bit of time clicking that paint pot. To remove gridlines, you can untick the box and remove gridlines.
Another great discovery that I wish I knew earlier. If you double click on format painter you can paste that format multiple times, rather than just once. This functionality works on all Microsoft programmes, saving a lot of time on Emails, Word documents and PowerPoint presentations
Sum will probably be the function you use most in Excel, so why not be quick using it? By pressing “Alt + =” this will AutoSum those numbers above.
Often, we will want to repeat an action multiple times, such as adding the same border, applying a fill or inserting a row. Instead of continually repeating this action we can simply press F4 and that’ll be repeat the action that you just did.
This combo of shortcuts is great to learn where information is coming from. Using Ctrl + [ will take you the cell’s precedents. F5 will pull up the Go to menu, however the reference stored will be the last cell you were in, so when you press Enter and you will return to the original cell.
Formatting and organising shapes and charts to look nice and orderly can be a difficult task however a few easy was to make them look good are to align and to Snap to Grid. To align any number of images, you can select multiple, by holding Ctrl and clicking each image, following this you can align the shapes accordingly.
With the same menu you can snap to grid, which will align the shape to the gridlines, however alternative you can hold Ctrl while resizing the shape with your mouse and this will also Snap to Grid.
Utilising formats will make your spreadsheets look a lot more professional and will help you achieve consistency in your work. The cell styles can be found within the “Home” part of the top ribbon or you can guide yourself there with your keyboard using Alt+H+J.
The standard cell styles that Microsoft gives you are limited styles however you can develop your own custom styles. We have provided you a set of styles to get you started which you can download here.
You can have this template file open automatically within the Excel settings. Instructions on how to do this are provided in this article from Microsoft.
Another important thing to become familiar with is Paste Special. This will save you a lot of time, especially when you start developing more complex models and worksheets.
Paste Special can be accessed after you have copied a cell, to access the menu you can press Ctrl + Alt + V all together.
Paste Special allows you to paste certain elements of the cell rather than just pasting the whole cell. This means for example you can paste only the format or the values of that cell. Paste special also allows you to do operations with the cell selected, such as multiply by the copied cell. You can also transpose the range of cells, so you can switch a list from down the page to across the page. Lastly you can paste the cell reference and thus make it easy to link model inputs throughout your spreadsheets.
To get quicker while using Excel it’s important to become proficient on your keyboard and use your mouse less. One way of learning the shortcuts is to hover over the icons in the ribbon, these will often show the keyboard shortcut to use.
Although we all like to think we are at the frontier of problem solving, I promise that someone, somewhere has had the same issue on Excel and is keen enough to write about it. Google is a very good friend for anyone using Excel, if you need to know how to do something get on Google.
This website cannot function properly without these cookies.
Analytical cookies help us enhance our website by collecting information on its usage.
We use marketing cookies to increase the relevancy of our advertising campaigns.