Browse by Tags

Related Posts
  • Blog Post: 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...
  • Blog Post: Formula Watch: Working with week numbers

    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...
  • Blog Post: Formula Watch - Automatically translate dates to days of the week in Excel

    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...
  • Blog Post: Formula Watch: Turn off rounding with TRUNC

    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...
  • Blog Post: Formula Watch: Picking random numbers in Excel

    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...
  • Blog Post: Quickly clear Excel spreadsheet values but keep your text and formulas

    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...
  • Blog Post: 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...
  • Blog Post: Formula Watch: Keep an eye on large and small values with MIN and MAX

    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...
  • Blog Post: 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...
  • Blog Post: Formula Watch: Calculating elapsed time in Excel

    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...
  • Blog Post: 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...
  • Blog Post: Formula Watch - Autonumber your rows and then convert them to values

    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...
  • Blog Post: Formula Watch: Hide customer data with CONCATENATE

    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...
  • Blog Post: Formula Watch: Use the Cell function to check column width, column number, and more

    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...
  • Blog Post: 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 records are in a spreadsheet...
  • Blog Post: 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...
  • Blog Post: Formula Watch: TRIM and CLEAN your Excel data

    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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: Formula Watch: Control rounding with CEILING and FLOOR

    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...
  • Blog Post: 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. Just select cells in a column...
  • Blog Post: Formula Watch: Using NOW to timestamp your Excel documents - plus shortcuts

    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...
  • Blog Post: 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...