Browse by Tags

Related Posts
  • Blog Post: Project Management with Excel: Your first Gantt chart

    Once you've built a basic schedule , you can greatly improve its usefulness by adding a Gantt chart. This is basically just a bar chart that visually displays your project schedule tasks and the dependencies between them: Excel 2007 doesn't have full Gantt chart capability out of the box, but there is...
  • Blog Post: Project Management with Excel - Building a schedule

    Are you ready for an intermediate Excel project? This one is fun, very useful, and will teach you a number of handy formulas for manipulating dates in Excel. I recently had to build a schedule and, in the process, developed a great little system that calculates your dates based on the number of working...
  • Blog Post: Excelling at Excel - Use Filters to home in on the data you need

    If you have a lot of data in an Excel worksheet, you should generally activate filters to make it more manageable to use. To do so, simply select any cell in the worksheet that contains data, click over to the Data tab, and click the Filter button. Your top row (usually a header row) will now have some...
  • 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: Casual Friday: Create an allowance chart in Excel

    A colleague of mine shared this handy Excel tip he uses to reward good behavior and chore performance to calculate his child's weekly allowance. He started it when his son was only about three years old, using fun stickers to mark simple chores and good days of listening and following directions. Here...
  • Blog Post: Restrict data entry in Excel with lists

    I recently shared how you can restrict data input in Excel to certain number ranges , such as whole number percentages. But what if you're dealing with a strict set of values, like product names, and you don't want users to be able to create any new variations when entering data? That's easy to do too...
  • Blog Post: Print Excel table headers on every page

    If you're printing a long spreadsheet in Excel and want to have the same header row across the top of each page, you're in luck. There's a little-known feature tucked away in Excel 2007 and Excel 2010 to do just that. If you're using Excel 2007, go to File , Page Setup and then open the last tab. In...
  • Blog Post: Excelling at Excel - Getting started with PivotTables: fields and areas

    Last week, we overcame one of the main obstacles to understanding and getting good value out of Excel PivotTables: optimizing your data . With that done, let's look at the fields required to make a useful PivotTable. If you followed along with my example, you should have an empty PivotTable with all...
  • Blog Post: Remove formatting, hyperlinks, and comments in Excel

    Clearing out cells of font mismatches, fill colors, hyperlinks, comments, and conditional formatting doesn't have to be tedious. There's a much easier way. First select the cells you want to clear. In Excel 2007, you need to go to the Edit menu and select the Clear command to access Clear Formats . In...
  • Blog Post: Boost your Office skills with Ribbon Hero

    I just downloaded Ribbon Hero , a free add-in for Office 2007 and 2010 that scores your usage and trains you to use new features through fun challenges. The more advanced features you use, the more points you score. Once installed, you'll find a new Ribbon Hero buttons at the far right of your Ribbon...
  • Blog Post: Prepare Office documents for download with the Document Inspector

    Before you publicly share a Microsoft Office document (e.g., a Word file offered for download on your public Web site), you should use the Document Inspector to ensure there are no hidden comments, invisible images, or personal information. Your first step should be to make a copy of the file you want...
  • Blog Post: Make your Excel rows and columns consistent

    As you enter data into cells in your Excel worksheet, you may notice that your rows change height - which can lead to a sloppy presentation if you're sharing the data. Likewise, you may want to adjust your column widths to make them more consistent. There's a quick way to do this. Simply select and right...
  • Blog Post: Use strikethrough to mark tasks done in Excel

    If you use Microsoft Excel to track tasks, you'll probably want to use Strikethrough to mark them as complete. Alas, the Strikethrough command does not appear conveniently on the Font section of the Ribbon as it does in Microsoft Word. There are three quick ways to access it. 1) The fastest is the Shortcut...
  • 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: Excelling at Excel - Save time with the multi-functional status bar

    If you're not using the Excel status bar, you're missing out on a number of time-saving tricks. The status bar runs along the bottom of the program, displaying contextual information and offering some handy shortcuts. Want to create a macro like the hyperlink removal trick we recently shared? Just...
  • Blog Post: Excelling at Excel - Turning your data into a meaningful PivotChart

    As we've seen in our examination on how to analyze your data , PivotTables are great when you want to dig into the numbers. But what if you want to present your data in a meaningful way for, say, an executive PowerPoint presentation. You could make a regular chart. But if you don't know exactly the best...
  • Blog Post: Casual Friday - Track World Cup soccer in Excel

    This week, I'm introducing a new occasional feature: Casual Friday. In it, I'll share uses for the Office applications that we've all put to such good use being productive at work to enrich our personal lives as well. I recently found just such a use over at the Microsoft Excel blog, where they've released...
  • Blog Post: Customize and backup your Office 2010 ribbons

    If you are a fan of the Office Ribbon, you have an unprecedented amount of control over it in Office 2010. If the default Ribbon doesn't offer up your favorite commands, you can simply create custom tabs and groups to easily access your favorite features. To ensure a base level of consistency, you cannot...
  • Blog Post: Lock down your Office documents

    If you're responsible for sensitive corporate documents, you might be tempted to just password protect them. But then you can't share them very easily, and you have to remember that password! If what you're trying to do is share documents internally but prevent them from being saved, printed or screen...
  • Blog Post: Changing case in Excel

    If you had to choose a program to use for changing case (UPPERCASE, lowercase, Sentence case, etc.) Microsoft Word works best in most cases. It has several options available, and the changes you make are retained if you copy and paste them. But Excel can be useful at this task too, if you understand...
  • Blog Post: Excelling at Excel - Hidden cells and subtotals

    When you're working in complex Excel workbooks, you may feel like you're getting lost in the clutter. There is a way to hide what you don't need to see so you can focus on the data you're really working with. To hide a column or row, right-click its letter or number and select Hide from the dropdown...
  • Blog Post: Excelling at Excel - Storing numbers that are longer than Excel allows

    Have you ever pasted a number into Excel and watched it turn into something like this: That's not terribly useful if you're storing a product ID or tracking number in a spreadsheet. Worse, it changes the number itself if it exceeds 15 characters. 123456789012345678 becomes 123456789012345000; the last...
  • Blog Post: Replace errors in Excel

    If you're tired of seeing error messages in your Excel 2007 cells, there's a quick way to change them to whatever you'd like to appear. Just use IFERROR. In this example, the Percent column has four results. In C2 , you can see a correct result. In C3 , there's an ugly divide by zero error (caused, in...
  • Blog Post: Make your own custom spell-check dictionary

    Are you slowed down when you spell-check your documents by words that you use every day that it just doesn't know? You could simply right-click them and select Add to Dictionary . Problem solved. From now on, the words you've added will be known to your Microsoft Word spell-checker. But what if you want...
  • Blog Post: Oops! I Forgot to Save It!

    One of the best things about Office programs (and just about any other application, for that matter) is that when I close a file without saving, I get prompted with a familiar message: It’s simple, I know, but I can’t tell you how many times this has saved my bacon. I also can’t...