The Productivity Hub
Office for Mac 2011
Browse by Tags
The Productivity Hub
Tagged Content List
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...
21 Jun 2011
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...
10 May 2011
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...
26 Apr 2011
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...
12 Apr 2011
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...
29 Mar 2011
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...
17 Mar 2011
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...
15 Mar 2011
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...
7 Mar 2011
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...
1 Mar 2011
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...
15 Feb 2011
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...
8 Feb 2011
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...
25 Jan 2011
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...
11 Jan 2011
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...
7 Dec 2010
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...
23 Nov 2010
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...
9 Nov 2010
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...
26 Oct 2010
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...
12 Oct 2010
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...
28 Sep 2010
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...
21 Sep 2010
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...
14 Sep 2010
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...
31 Aug 2010
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...
17 Aug 2010
Page 1 of 1 (23 items)
© 2013 Microsoft Corporation.
Privacy & Cookies