Excel’s default number formats don’t really work for accounting and finance reports. There is no way any accountant in their right mind would use the Accounting format. Instead, we must develop our own custom number formats with the brackets, decimals, and alignment in exactly the right place. So, I want to share with you the … Read more
We come across drop-down lists on the internet and other software applications all the time. They are useful because they limit the options available to the user, as only pre-validated items can be selected. In this post, we’ll look at how to add a drop-down list in Excel. In Excel, there are three different types of … Read more
Excel Tables expand automatically whenever new data is added to the bottom. This feature alone makes Tables one of the most powerful tools within the Excel users toolkit. A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data validation lists would … Read more
When most people use PivotTables, they copy the source data into a worksheet, then carry out lots of VLOOKUPs to get the categorization columns into the data set. After that, the data is ready, we can create a PivotTable, and the analysis can start. But we don’t need to do all those VLOOKUPs anymore. Instead, we can … Read more
Back in 2007, the Excel data revolution started. At the time we saw a few new features, a new menu layout, a new file format, and 983,040 additional rows. We had no idea of the plans Microsoft had for Excel, but it was all laying the groundwork for what was to come. In the following … Read more
If you speak to anybody who works in the accountancy field, they will always be talking about the “month end”. In all medium and large companies there will be a process at each month end which requires statements to be issued and reports to be distributed. This monthly process fills most finance people with a … Read more
I’ve posted many times about named ranges in various guises. However, one thing I have not covered is relative named ranges. Relative named ranges don’t sound too exciting, but once you’ve read this post I’m sure there will be plenty of examples where you could have used them and now wish you had. Creating named … Read more
Happy Spreadsheet Day! What are your feelings about allocating days to celebration or awareness of certain things? Like International Talk Like a Pirate Day (19th September), Oatmeal Nut Waffles Day (11th March) or Bathtub Day (7th October)? Well, the 17 October is International Spreadsheet day. Normally, I would think these days are pointless, but I … Read more
Do you ever find yourself typing out the same standard lists over and over again, such as lists of products, cost centers, projects or companies? I do. It is essential these lists don’t exclude any items and are presented in the same order each time. Lists of 4 or 5 things are easy to remember, … Read more
If there is one thing I am certain of, it is this: There is always something new to learn in Excel. Some of those things will be small little tips and tricks, which might help you a bit, but other things you learn will be game changers. These game changers are the things which save … Read more