Category Archives: Beginner

Beginner level information

Date Format Settings Excel Windows

Regional Date Formats in Excel

I recently returned from two weeks in Ireland where I installed a couple of Excel reporting systems and came across the dreaded regional date settings. My computer is set for the US region, whereas the client’s computers were set for the UK region. When I mention regional settings on my computer, I’m talking about Windows and not Excel.

Trying to get Excel to automatically switch date formats between computers using different region settings was something I experienced a few years ago while working in England. Back then I made the mistake of ustng Excel to change the formatting for … Read the rest

Spreadsheet Size in Excel for Windows

I’ve been thinking about converting my older XLS files to the newer XLSX format, but ran into a quirk that stopped me. Maybe I’m just too impatient. Here’s my story.

A Few Facts First

Excel versions 97-2003 (Windows) have a file extension of XLS and the worksheet size is 65,536 rows and 256 columns. In Excel 2007 and 2010 the default file extension is XLSX and the worksheet size is 1,048,576 rows and 16,384 columns.

An XLSX File with Only 65,536 Rows

I open Excel 2003, create the following spreadsheet, and save the file. The formulas are listed in my … Read the rest

Spreadsheet Size in Excel for Mac

Most people think an Excel workbook with an XLS file extension has only 65,636 rows and 256 columns in the worksheet. They would be wrong. Of course, if you don’t use a Mac there’s no way for you to discover this fact.

When you open an Excel 97-2003 Workbook (*.xls) file in Excel 2007 or 2010 (Windows) it’s done in Compatibility Mode, which you can see at the top of the Excel window.

Compatibility Mode

I put together a few formulas to show the size of the worksheet, the Excel version, Operating System version, and System info.

XLS file in Excel 2010

This information is from … Read the rest

Excel Everest Icon

5 Obvious Things that 80% of People Don’t Know About Excel

This is a Guest Post by Excel Everest.

Excel Everest Icon

ExcelEverest.com | For whatever reason, most people remain unaware of these 5 ultra-simple and ultra-useful aspects of Excel.

Many people use Excel on a daily, or at least a weekly basis. But many of these people also haven’t had or taken the time to dig into even the simple workings of Excel. Through our quest to build the ultimate Excel tutorial, our company, Excel Everest, has noticed five things that you’d expect people to know about Excel, but don’t. Spend 10 minutes to learn these and you’ll already have a leg … Read the rest

This entry was posted in Beginner on by .

Table Names in Excel

Excel Tables are given default names (Table1, Table2, etc.) when they are created. Table names are similar to defined names, which makes them easy use in formulas or to select data.

There are a couple of ways to select all the data in a Table by using the Table name. 1) select the Table name from the Name box,

Name Box Both

or 2) use the keyboard shortcut Ctrl+G then select the Table name and click OK.

Go To Dialog Box

Table names can be used in formulas =ROWS(Table1) and they show up in the formula autocomplete list.

Table Name in Formula

A Table name in Excel 2010 (Windows) is distinctly … Read the rest

This entry was posted in Beginner on by .

Table Design in Excel

I’m want to start using Excel Tables when building my spreadsheets. But first it’s instructive for me to explain how I think about “unofficial” tables or data tables as I like to call them.

How I Think About Tables

A data table is just a range of data, or a region of data that Excel recognizes if you were to use the keyboard shortcut Ctrl+Shift+*.

There are headings in the top row, which  I refer to as column headings or data fields. Date, Shift, Machine, and Total Hours are all examples. The Date column has dates in … Read the rest

Migrating to Tables in Excel

My first epiphany with Excel was realizing how powerful it could be if you put all your data into data tables. Over the years Excel began to incorporate data tables. First in Excel 2003 as “Lists” then as official “Tables” in Excel 2007. (I’m not sure about the evolution in Excel for Mac versions.)

The reason I haven’t done more than piddle around with Tables is that common Excel users aren’t typically early adopters AND when you design data input and reporting spreadsheets you always design for the least common denominator (with regard to Excel versions.)

But I’m beginning to … Read the rest

Set the Number of Default Worksheets in Your Workbook

I opened Excel 2007 today and realized there were 3 worksheets in a new workbook. That’s better than the 16 or so Excel had in times past, but more than one worksheet in a workbook is overkill, if you ask me.

Default Worksheets in Excel Workbook

To change this travesty in Excel 2007, I’ll choose the Office Button > Excel Options. In the Excel Options dialog box the Popular tab is already highlighted, so under the section for When creating new workbooks I will change the default setting for Include this many sheets to the value 1. There. Problem solved.

Default Sheets in New Workbook Setting

In Excel for Mac… Read the rest

This entry was posted in Beginner on by .

View Excel Spreadsheets with the Google Docs Viewer

If you have a Gmail or Google Docs account, getting a quick look at a Microsoft Excel file is now supported with the Google Docs Viewer. Just click the View link beside the file name in your email and the Google Docs Viewer will open to give you a quick look.

Google Docs Viewer in Mail

Now you can send Excel files to people who have a Gmail or Google Docs account and they will be able to view your spreadsheet information. Not the greatest view of a spreadsheet file, but it’s better than nothing.

Google Docs Viewer with Excel File

In the Google Docs Viewer you have the option … Read the rest

Convert Coordinates for Your GPS with Excel

A few years ago I found myself just south of Paris, France one Sunday with a car and the inclination to do some sight-seeing. Since I was alone and don’t speak French my saving grace was Google Earth and a Tom Tom GPS navigation device.

Tom Tom allows GPS coordinates to be entered as a destination, but Google Earth lists those coordinates in a different format. In researching this article I found out that GPS coordinates can be presented in at least four different formats, making it difficult to understand the coordinates.

Google Maps view of the Eiffel

… Read the rest

Open File As Read Only in Excel

To discourage users from making any changes to a macro file you can have the file recommend Read-Only when it’s opened.

Open as Read Only

To show this pop-up box when the file opens:

  • Choose File > Save As
  • Select Tools > General Options (on the Save As dialog box)
  • Check the box for Read-only recommended
  • Click OK
  • Name the file and click Save

General Save Options

If you select Yes to “Open as Read-only?” and try to save the file you get a pop-up screen asking you to give the workbook a new name in the Save As dialog box.

Read Only Save As

Of course, selecting No to “Open … Read the rest

My First Excel 2011 Chart

Right now I’m on the Ski slope and this particular chart served to help me kill two birds with one stone. I needed to show my daughter which ski pass would be most beneficial to her wallet and produce a chart for this article.

Charts should speak for themselves, loud and clear. This one compares the cumulative cost for an Advantage Pass and a Standard lift ticket. The Pass is $69 and your first lift ticket is free, then you get $12 off each weekday lift ticket. A standard lift ticket is $60 each weekday.

Stevens Pass Break Even

And while I started out … Read the rest

The Mini Toolbar in Excel

The Mini Toolbar is nice to have when you need to do basic formatting and the Home tab is not displayed. Introduced in Excel 2007 and available in Excel 2010, the Mini Toolbar appears above the shortcut menu when you right-click a cell, range, chart, shape or other object.

Note: There is no Mini Toolbar in Mac versions of Excel (2011, 2008).

The contents of the Mini Toolbar are dependent on the object you right-click.

Right-Click a Range

I frequently Bold and Center format my table headings, but when the Home tab is not displayed it’s quicker to use the … Read the rest

F2 in Excel for Mac

Switching to a Mac has its advantages but Excel shortcuts isn’t one of them. The main gripe I have is that keyboard shortcuts should be the same in every version of Excel, yet the Windows and Mac worlds are light-years apart. The F2 key is a fundamental shortcut I learned early in my Excel life. It edits the active cell and positions the cursor at the end of the cell contents.

You want to edit a formula, hit the F2 key. But wait! In Excel for Mac the F2 key cuts text from the active cell. How could they … Read the rest

Add Freeze Sheet Panes to the Quick Access Toolbar

Add a one-click Freeze Panes command to the Quick Access Toolbar in Excel 2010 and 2007. This command icon is conveniently hidden from the Ribbon and is named Freeze Sheet Panes.

In a previous post I made the recommendation to add the Freeze Panes command to the Excel Quick Access Toolbar, but recently found out that this is a sub-optimal solution because with the Freeze Panes command this requires two clicks.

One click activates a drop-down list, then you have a choice for your second click: Freeze Panes, Freeze Top Row, and Freeze First Column. The only one of … Read the rest