Formula Watch – Working with DOLLARs

In most cases, you want to keep your dollar values in Excel as numbers so they can be used in math functions. But there may be cases where you want your dollars converted to text, say to concatenate them with other text strings. Just use the DOLLAR function: =DOLLAR(B10,2) This would take the value in…


Formula Watch: Create custom placeholders with REPT

When filling in empty cells with placeholders or inserting leader characters between columns, try the REPT function. You simply specify a character or string and the number of times you want it to repeat (up to certain limits for Excel, well into the thousands of characters). For instance, if you’re entering data in the millions…


Formula Watch: Lock Excel formulas so they don’t change when you paste

How many times has this happened to you? You’re copying and pasting some Excel functions and they change to reflect the cells relative to where you pasted them, but you want the original values intact. There’s a neat trick that will make this problem quickly vanish. Just use the F4 key. Here’s how it works:…


Formula Watch: Quickly CONVERT measurements in Excel

If you’ve ever need to translate Celsius to Fahrenheit, miles to kilometers, or teaspoons to cups, all you need is the handy CONVERT function in Excel. Simply enter your value, its measurement unit, and the target unit for conversion, and you’ll get your answer. Here are some useful examples: =CONVERT(30.4,"C","F") =CONVERT(65,"mi","km") =CONVERT(10,"tsp","cup") You don’t need…


Formula Watch: Convert to and from ASCII codes in Excel

If you ever need to look up an ASCII code, say for an HTML entity such as a © or ® symbol, you can simply use the CODE command in Excel: =CODE("©") This returns a value of 169, so your HTML code would be: © It’s easy to make your own ASCII chart in Excel….


Adding times together in Excel

In an earlier tip Calculating elapsed time in Excel, I shared a quick and easy formula for determining the difference between two times. The advantage of that approach is that it doesn’t require you to format the cells. The downside is – as Nicky, one of our readers, noted in comments – that you’re converting…


Formula Watch: COUNT your cells the easy way

If you want to get a quick count of your cells, you probably use the Excel status bar. But if you want to include the results in a report or summary, there’s an even better way: the COUNT function. There are all sorts of applications for this. You can use it to count how many…


Optimize performance in Excel 2010

If you’re an Excel 2010 power user or just want some insight into how certain features can affect performance, you will enjoy this MSDN article: Excel 2010 Performance: Tips for Optimizing Performance Obstructions Here are some quick tips from the article to help you get started: Use the XLSB file format for slightly faster opening…


Formula Watch – Convert numbers to and from Roman numerals

Once you get past 20 or so, converting Roman numerals to Arabic (or vice versa) can be tricky. But you can save time and improve accuracy with a pair of Excel functions. To convert an Arabic number to its classic Roman numeral, use: =ROMAN(A1) Naturally, you can place a number directly into the parentheses or…


Turn Formula AutoComplete on and off in Excel

If you’re a formula pro, you may find that the AutoComplete feature gets in the way more than it helps. To turn it off, go to File, Options, Formulas in Excel 2010 (look under the Office button in Excel 2007) and uncheck the box next to Formula AutoComplete: Don’t worry, this just sets your default….