Paste Values
After copying, use the following keyboard combination to paste just the values - no formulas or formats. Alt h v v These keys are pressed in sequence, not held down.
Random Dates
In Excel dates are stored as numbers. To generate random dates you can use the RANDBETWEEN function with a start date and an end date. The formula in cell C2 is =RANDBETWEEN(A2,B2) Cell C2 has been formatted as a date. This formula is dynamic. Each time Excel calculates it will update and most likely change. You can use Copy > Paste Values to capture the date(s).
Working with Imported Dates and Times in Excel
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy. (more…)
Counting Errors in a Range in Excel
If you need to ensure that a range has no errors you can count the errors and compare the result to zero to ensure the range is error free. The formula in cell C1 which counts errors in a range is =SUMPRODUCT(ISERROR(A1:A5)*1) If you want to display TRUE for no errors and FALSE for error(s) you can use =SUMPRODUCT(ISERROR(A1:A5)*1)=0 You can also use an IF function to display the text Error if any errors are found. =IF(SUMPRODUCT(ISERROR(A1:A5)*1)=0,"OK","Error")
Using a Number as Text in Excel Made Easy
When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task. (more…)
Removing Line Feeds in Excel
Sometimes when text data is imported from other systems it can contain line feeds. A line feed wraps the text onto a new line - it is not associated with the Wrap Text format. Cell A1 below shows an example. Cell B1 has the formula that removes line feeds and replaces it with a space. The formula in B1 is =SUBSTITUTE(A1,CHAR(10)," ")