There are shortcuts to enter the current Date Ctr + ; the current Time Ctrl + Shift + : There isn't one to enter both. You can use them in sequence to achieve a date and a time entry. In sequence press Ctrl + ; Space bar Ctrl + Shift + : The space separates the date and time and Excel will recognise the entry as a date and time.
Let’s say you have codes that have differing numbers of characters and you need to analyse them based on how many characters a code has. There is one function that can SUM and COUNT based on the number of characters in a code. (more…)
If you change a formula in someone else’s file you should tell them. In fact there are a few things you should do to make it easier. This can apply to changes to your own files as well.
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates. (more…)
Power Query morphed into Get & Transform and now in the latest Excel 2016 subscription release Get & Transform has expanded on the Data Ribbon.
![Read more about the article Creating a table of all combinations in Excel [Video]](https://a4accounting.com.au/wp-content/uploads/2018/01/vimeo-250571093.jpg)
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table. (more…)
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
(more…)In Excel the "Blanks" option is usually at the bottom of the list. This slows down selecting it. If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it - see image below. But the word "Blanks" is searchable, so if you type b in the Search box - your work is done - no scrolling required - see image below. If your column contains text you might…
There are certain situations when you can’t “unhide” columns on the left and/or rows at the top and it is not a sheet protection issue. (more…)