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...

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...

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...

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...

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. Just select cells in a column...

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...

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 records are in a spreadsheet...

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...

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...

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...

If you import data into Excel or just have a lot of different people working in the same worksheet, you may sometimes find unnecessary spaces, numbers entered as text, and other irregularities. Fortunately, there's a simple set of functions that can take care of most of your basic data formatting issues...

If you work with currency, you may have noticed that the default behavior in Excel is to round up cents. This may work in most situations, but if you ever want to truncate the value without rounding, you'll want to use the TRUNC function. For instance, let's say you are dividing a $119.99 subscription...

Privacy is an important watchword these days, and Excel can help you hide sensitive customer information such as Social Security Numbers or credit card numbers. Simply use CONCATENATE to combine a generic text string representing the missing digits with the last four digits. That's enough information...

If you just want to spot check the smallest and largest values in a selected range of Excel cells, you can simply add MINIMUM and MAXIMUM to the status bar . But if you want these values to appear in your reports, you can save time by using the MIN and MAX functions. Decide on where you want to place...

If you want to record the time and date while working in Excel, you can use the =NOW() function. Just enter it into any cell: There's only one problem. As you work in Excel, the NOW function will continue to update. This would be great for keeping track of the current time and date if you didn't already...

If you round decimal places away and want full control over which way they go (up or down), you should use the CEILING and FLOOR functions. Here you can see the first number 38.1 is rounded down to 38 while the second percentage 42.70% is rounded up to 43%: If you always want to round a number up, use...

If you've ever wanted to have a number column that automatically updated as you inserted new rows, simply use this function: =row() Place that in your A1 cell and then either cut and paste or autofill down as far as you like. If you insert rows, the numbers will update to match the row number. If, at...

If you ever need a random number - say to pick a place for lunch, flip a coin, or select someone for a prize or work duty - you could just enter this handy little function into Excel: =RANDBETWEEN(1,10) The above function picks a number between 1 and 10, but you could select any numbers you want. For...

There are plenty of scenarios where you'd want to know the week number within a year. Say you are developing a schedule with a time budget, and you need to know how many hours you can commit per week over the course of a project or fiscal year. Or maybe you just want to know how many weeks are left until...

If you have an Excel spreadsheet that you want to re-use without all of the data (say, to share with colleagues or use as a generic input form), you'll want to keep all of the formulas and text fields intact but clear the input values. The quickest way to do this is with the Go to Special command, which...

If you use Microsoft Excel to log times, there's a simple technique you can use to quickly calculate the difference between two times. For instance, you might log the start and end times for meetings, or keep track of the entry and exit times for visitors to your office space. One simple formula can...

If you want to know more about the size, color, and contents of your cells in Excel, you should get to know the Cell function. For instance, to find out how wide a column is, just enter this into one of its cells: =CELL("width") If you want to list the filename and path in a cell, say to identify...

If you're working in an Excel document that contains lots of dates and find yourself checking the calendar to see what day of the week each one falls on, there's a much easier way. A simple formula can save minutes, maybe even hours of your life you'd otherwise spend grabbing the datebook or clicking...