Learn Microsoft Excel

Formatting dates in Excel

Date formatting in Excel can be a tricky business at first. Excel often seems to have a mind of its own when formatting dates.
Automatic date formatting
When you enter a date into a spreadsheet, Excel will try to guess what the format of the date should be. For example:
If you enter April 09, Excel sees that as the first of April 2009, and will format it as Apr-09.
If you enter 1 april 09, Excel will format it as 1-Apr-09
 

Absolute and relative references in Excel

When you create a formula in Excel that refers to other cells in the worksheet, Excel will store the information about those cells as relative references. Relative references and their counterpart, absolute references, are one of the that make spreadsheets such a powerful tool.

Using the SUBTOTAL() function with the Autofilter feature

If you're using the Autofilter feature, you're probably wondering how to perform calculations on only those values that are being displayed by the filter. The SUBTOTAL() function is the answer.

Calculate a value that combines text and numbers

If you want to combine text with the results of a formula in a cell, you can use concatenation.

Introduction to using Autofilter in Excel

Autofilter is one of the most powerful features of Excel. It lets you treat a range of cells as a table and then filter out certain rows based on different criteria.

Calculating the current date and time in a worksheet cell

If you want to enter the current date and/or time into a cell so that it automatically stays current, use the NOW() or TODAY() formulas.

Calculate a running total of a column of cells in Excel

If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use of absolute and relative references.

Add up a column of cells in Excel

There are a variety of ways to add up a range of cells in Excel, whether that be a column, row or selection of unconnected cells.

Quickly enter a formula in multiple cells

Entering the same formula or value into multiple cells can be time consuming and boring. Most people, if they want to enter the same formula into a row or column of data, will enter the formula in the first cell, then copy and paste it into the rest of the cells where they need the formula. This lesson shows you the quick way to do it.

Extract text from a cell in Excel

Posted Tue, 01/06/2009 - 19:37 by millionleaves
0
Your rating: None

Excel offers a range of text manipulation formulas that allow you to extract data from within a cell. This article discusses two of them, LEFT() and RIGHT().

Scenario: 

You have one or more cells containing text or a combination of text and numbers. You want a fast way to extract part of the value in each cell without manually editing each cell.

Select all the blank cells within a range of cells

If you have a range of cells, of which some contain values and some are blank, and you want to select just the blank cells, there is a quick way to select those blank cells that doesn't involve manually clicking on every one.

Use the keyboard to move between worksheets in a workbook

One of the last keyboard shortcuts I mastered in Excel was moving between worksheets. Fortunately it's easy, and you don't need to wait as long as I did.

Select cells quickly and easily in Excel using the keyboard instead of the mouse

Once you get used to using Excel, you can find that using the mouse to select data in your spreadsheet is somewhat slow and time consuming. Here's a quick technique for selecting cells in Excel.

Scale your Excel spreadsheet to fit your screen

If you work with large Excel spreadsheets, you'll probably know the hassle of scrolling left and right, up and down as you try to work with all that data. You can use the Zoom feature to make the spreadsheet smaller and fit more onto the screen, but that doesn't always give you the result you want. Often, it will make your spreadsheet too small or not small enough.

Scale your spreadsheet to fit when printing from Excel

Printing from Excel can be very frustrating, especially if your spreadsheet is too wide or too tall to fit on a single page.

You can use the Scaling option in Page Setup to set limits on how many pages wide and tall your document should be when you print it. The problem with that is that you can find your page fits onto one page, but becomes too small to read. Not only that, but Excel ignores any manual page breaks you've entered.

About Us

Fiveminutelessons.com is a website dedicated to providing free training on a range of subjects. We're starting with lessons on Microsoft Word, Excel and PowerPoint but have plans to add other subjects over time such as Adobe Photoshop and Microsoft Project as well as non-software subjects.

We're based in Tauranga, New Zealand but have years of experience in the UK, USA and Ireland working in the training industry with a range of clients from small businesses to blue chip corporates.

To find out more, why not get in touch?

Commercial Services

As well as the free training you get at www.fiveminutelessons.com, we also offer a range of commercial services to help you with your training and online learning requirements including:

  • Private labelling
  • Courseware development
  • e-learning development
  • Corporate subscriptions
  • Learning consultancy services
  • Documentation and user guide development
  • Development of innovative corporate learning environments

We offer these services for Microsoft Word, Excel and PowerPoint, as well as a range of other popular software applications. We can also assist with documentation and courseware development for custom or in-house software packages.
Contact us now to find out more about our learning consultancy and courseware development services