VBA AutoComplete
When you are typing code in the VBA code window you can press Ctrl + Space Bar to have Excel finish the word for you. Eg type appli And then press Ctrl + Space Bar to have Excel finish the word Application.
Excel VBA Command to Clear the Clipboard
When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
(more…)Counting Positives and Negatives
If column A contains positive and negative numbers, you may need to count how many of each. You can use the following two formulas. Positives =COUNTIF(A:A,">0") Negatives =COUNTIF(A:A,"<0") Zeroes There may be zeroes as well. The formula to count them is =COUNTIF(A:A,0)
Generating Random Text Entries in Excel
In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.
(more…)Inserting values that start with 0
To enter numbers that starts with a zero eg mobile telephone numbers, start with the single inverted comma ' then type the number and press Enter. If you are going to enter a whole column of them, it may be easier to format the whole column as Text - see image below. When you use the Text format you don't need to use the inverted comma, just type the number.
Two Random Functions in Excel
Excel has had the RAND function for a long time. In Excel 2007 a new function was added. Called RANDBETWEEN it made it easier to create random numbers. (more…)
Calculation Warning
You have to be careful when turning off calculation in Excel. The calculation options are on the right of the Formulas ribbon tab - see image below. When you turn off calculation (change it to Manual) it affects all the open files, not just the active one. The reasons you must be careful are Most people don't turn off calculation and hence always expect the sheets to automatically calculate, so they may rely on the results even though they might…
Space Bar Tips
To select the current column or columns hold the Ctrl key down and press the Space Bar. (Remember tip - Ctrl + column both start with c) To select the current row or rows hold the Shift key down and press the Space Bar.
Excel’s Smart Art
Microsoft boosted Excel’s graphic engine and increased the chart formatting options and it also added Smart Art. Smart Art includes built-in graphics that represent different types of processes. (more…)