Excel Worksheet Tips: Keyboard Shortcuts I was searching for a particular shortcut but unfortunately I was unable to find the one I was looking for; however I have found many more. Check out all these useful shortcuts here. Come check out the directory for the…
Excel VBA Tips: Closing All Non-Active Workbooks If you ever need to close off all the workbooks you have open in the background but don’t want to have to navigate to each one, this code is extremely useful. Public Sub CloseAllInactive() Dim Wb As Workbook…
Excel VBA Tips: Large concatenation OR concatenating multiple ranges One of the useful worksheet functions in excel is being able to concatenate cells into a single cell. However, if you try to concatenate a large number of cells or a multiple ranges, it can become…
Excel VBA Tips: Remove Password from Worksheet There have been many times when I’ve password protected an excel worksheet or workbook and then forgotten what it was. Wanting to modify the sheet later, I then realise I can’t, as I don’t remember what the password…
Excel Worksheet Tips: Average Without Zeros Have you got a collection of data that you wish to take the average of, but do not want to include zeros? By including them, it will alter the average you are looking for. Excel 2003 =SUM(A1:A10)/COUNTIF(A1:A10,”<>0″) Excel 2007…
Excel Worksheet Tips: Hiding Buttons When Printing You may have a sheet that contains buttons, which when pressed, perform an action. When printing an active sheet, these buttons will be displayed on the printout. This may not be desired, as it doesn’t look professional. To…
Excel VBA Tips: Selecting the Default Printer in VBA I had created a document that is to be printed by multiple people in different locations. However, when pressing the print button that I created on the worksheet, I needed to ensure that the default printer…
Excel VBA tips: How to Run Macros When a Workbook or Worksheet is Protected Have you created a workbook which contains macros, but you wish to protect the sheet? When you protect the sheet, it stops the macros from running, which is really annoying. However,…
IT Tips – Clearing clipboard in Word and Excel 2007 I was wondering where that option had disappeared to. After some searching, I discovered how to clear the clipboard. 1. In the office application, click the home tab. 2. On the left hand side of…
Excel Chart Tips: Plotting With a Dynamic Range Another tip that I learnt today. When plotting a graph, the data that is to be plotted must be linked to the graph. What happens when the range of the data changes in length? You need to…
