New dynamic yearly calendar

I have added to the downloads page a new dynamic yearly calendar template.  You access the calendar through a user form which has two combo boxes: the first one to specify the year and the second  to select the start date of the week. You can generate a calendar by selecting any year between 2010 and 2030. You can also enter the year through the keyboard if it is not in the list. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:

  1. The calendar is compatible with Excel 2007 and 2010.
  2. You may receive a warning message that macros are disabled. You have to enable macros for this template to work properly.
  3. The “Calendar”  sheet is protected to avoid accidental change of data and formulas. If you want to make some changes, like formatting for example, then you can unprotect the sheet. Make sure however that you don’t change any formulas.
  4. You can print the calendar just like any other Excel worksheet. To avoid having the sheet split in two pages for any reason , make sure that in page layout settings you have the Scaling set to “Fit sheet on one page”.

Your feedback is appreciated.

 

Selected Excel 2010 shortcut keys

Below is a list of selected Excel 2010 shortcut keys. They all use the control (CTRL) key with a combination of other keys:

Key Description
CTRL+; Enters the current date.
CTRL+SHIFT+# Applies the Date format with the day, month, and year.
CTRL+SHIFT+: Enters the current time.
CTRL+SHIFT+@ Applies the Time format with the hour and minute, and AM or PM.
CTRL+SHIFT+Plus (+) Displays the Insert dialog box to insert blank cells.
CTRL+Minus (-) Displays the Delete dialog box to delete the selected cells.
CTRL+` Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+1 Displays the Format Cells dialog box.
CTRL+2 Applies or removes bold formatting.
CTRL+3 Applies or removes italic formatting.
CTRL+4 Applies or removes underlining.
CTRL+5 Applies or removes strikethrough.
CTRL+6 Alternates between hiding and displaying objects.
CTRL+8 Displays or hides the outline symbols.
CTRL+9 Hides the selected rows.
CTRL+0 Hides the selected columns.
CTRL+A Selects the entire worksheet
CTRL+B Applies or removes bold formatting.
CTRL+C Copies the selected cells.
CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F Displays the Find and Replace dialog box, with the Find tab selected.
CTRL+G Displays the Go To dialog box. F5 also displays this dialog box.
CTRL+H Displays the Find and Replace dialog box, with the Replace tab selected.
CTRL+I Applies or removes italic formatting.
CTRL+K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
CTRL+L Displays the Create Table dialog box.
CTRL+N Creates a new, blank workbook.
CTRL+O Displays the Open dialog box to open or find a file.
CTRL+SHIFT+O Selects all cells that contain comments.
CTRL+P Displays the Print tab in Microsoft Office Backstage view.
CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+S Saves the active file with its current file name, location, and file format.
CTRL+T Displays the Create Table dialog box.
CTRL+U Applies or removes underlining.
CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection.
CTRL+ALT+V Displays the Paste Special dialog box
CTRL+W Closes the selected workbook window.
CTRL+X Cuts the selected cells.
CTRL+Y Repeats the last command or action, if possible.
CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

 

 

How to import Access table into Excel 2007

  1. Open a new workbook, or an existing one and select a worksheet.
  2. On the Data tab, Get External Data group click From Access.
  3. The Select Data Source window will open. Select the Access database file and click Open.
  4. The Select Table window will open. Select the table you want to import and click OK.
  5. The Import Data window will open. Specify how you want to view the data and whether you want it in the existing worksheet or a new one. Click OK.
  6. Here is how your data will look like in Excel:

How to save excel worksheet as a comma delimited file

You can save an Excel worksheet as a comma delimited file. I.e. the worksheet columns will be saved in the file separated by commas. This will allow you or someone else who has no Excel, to browse the file in any text editor.

To save the worksheet as a comma delimited file:

1.       In Excel menu click File.

2.       Click Save As. The Save As window will open.

3.       In the Save as type drop down, select CSV (Comma delimited).

4.       Any row in the worksheet will be transformed like this:

“ABC;7268;87368;XYZ;373”

The delimiter is either a “,” or “;” depending on your installation of Excel.

Applies to: Excel 2007, Excel 2010

How to attach a file to Excel 2007 workbook

It is fairly easy to  insert a file into an  Excel worksheet as an attachment. That file can be a word document, PowerPoint presentation or even another Excel workbook.

Follow these steps:

  1. In Excel Insert tab, Text group click Object.
  2. The Object window will open.
  3. Select Create from File tab.
  4. Select the check box Display as Icon.
  5. Click Browse and select the file you want to attach.
  6. The file will be shown on the worksheet as an icon.

How to insert a shape in Excel 2007

To insert a shape (rectangle, circle, line … etc.) follow these steps:

  1. Select the Insert tab, then Click Shapes.
  2. XL7Shapes1

  3. A drop down menu will show different types of shapes.
  4. XL7Shapes2

  5. Select the shape you want. The mouse pointer will change to a cross like this :XL7Shapes3
  6. Draw the shape by pressing  the left mouse button then dragging the mouse.
  7. Release the left mouse button when finished.

How to install and load the Analysis Toolpak in Excel 2007

To install and load the Analysis Toolpak in Excel 2007 follow these steps:

  1. Click the Office button. XL7OffButton
  2. Click Excel Options, the Excel Options window will open.
  3. XL7Addin1

  4. In the left pane of Excel Options click Add-Ins, The right pane will show a list of Active and Inactive  Add-ins.
  5. At the bottom of the right pane there is a combo box (drop-down) labeled Manage. Select Excel Add-Ins from the drop-down then click GO. The Add-Ins window will open.
  6. XL7Addin2

  7. In the Add-Ins window select the check box that says: Analysis ToolPak, then click OK.

Copy Excel 2007 data to a Word document

If you want to copy data from Excel to Microsoft word then follow these steps:

In  Excel

  1. Select the worksheet data (range) that you want to copy to a Word document.
  2. On the Home tab, Clipboard group, click

How to change Excel 2007 default file location

To change Excel 2007 default file location, follow these steps:

1. Click the  Microsoft Office Button.

2. Click Excel Options.

3. In Excel Options window (left side) click Save.

4. In the text box labeled Default File Location , specify the path for the required location.

XL7Save

Save an Excel 2007 workbook to Excel 97-2003 format

To save an Excel 2007 workbook to Excel 97-2003 format follow these simple steps:

1. In Excel 2007, open the workbook that you want to save in Excel 97-2003 format.

2. Click the Microsoft Office Button XL7Office and point to the arrow next to Save As.

3. Under Save a copy of the document, click Excel 97-2003 Workbook.

The file will be saved in the Excel 97 – Excel 2003 file format. This will make it possible to open the file in versions of Excel 97 through Excel 2003.

XL7to2003