Time to upgrade my Spreadsheet Mug
The dishwasher has taken a heavy toll on my old mug. I think the new version has a slightly lower capacity.
The NETWORKDAYS.INTL Function Has a Unique Trick
The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends. (more…)
Increase Decimal
I found a good shortcut today to increase the decimal places. Alt H 0 For example you might use these two shortcuts one after the other to format a range as a percentage with one decimal place. Ctrl + Shift + % Alt H 0
Trailing Minus For Negatives in CSV vs TXT Files
Some Accounting systems (I think SAP is one) downloads negative values with a trailing minus sign. Excel doesn’t recognise this as a number. When you import TXT files, negatives are handled correctly. CSV files don’t. (more…)
Making Subtotals Bold
When you use the SUBTOTAL feature in the Data ribbon tab it automatically inserts subtotals in your list - see blog post on it here. One problem with this is that is only makes the cell with the word Total bold - it doesn't make the whole row bold. If you want the whole row to be bold it isn't hard to fix. Select the whole range involved. Use the grouping button 2 top left corner. See image below. Then…
Alt Key Shortcuts
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones. (more…)
Printing Separate Print Areas
If you need to print a number of separate print areas from the one worksheet in one step there is an option that allows you to create a print area that includes additional ranges. Create your first print area as per normal. Select another print range and click the Page Layout ribbon and use the Print Area drop down and choose Add to Print Area. Keep adding ranges as you require. You can also use the Ctrl key and mouse…
Export a sheet as a PDF
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.) (more…)
Close All Icon
If you have a lot of files open and you want to close them all and still leave Excel open you can add this icon to the Quick Access toolbar. See this post if you are not sure about using the Quick Access Toolbar.