jump to navigation

Prep a Workbook to Save as a Template September 24, 2016

Posted by computertrainer in Excel Tips, Office 2007 Tips, Office 2010 Tips, Office 2013.
Tags: , ,
add a comment

This Blog is for a favorite non-profit in Manchester, NH …

You have an existing workbook that you need to replace the old data in all the cells with “constant numbers” – maybe it is daily, weekly, monthly, quarterly – frequency doesn’t matter. How did you do this in the past?  Maybe you:

  • Opened the last workbook and saved it with a new name
  • Then you started typing in the new data

I did that years ago until one time when I was handed the new data to enter just as my boss was heading into the meeting where the workbook was needed. So I started entering the new data UNTIL the phone rang and I got distracted.  I thought I was done so I saved the workbook, printed it on an overhead, and took it into the meeting for my boss.  A few minutes later he came out asking WHY ARE LAST WEEKS NUMBER ON THE BOTTOM PART OF THIS REPORT?

Simple answer – I was distracted by the phone. So I promised I’d find a way so this would never happen again.

The SOLUTION – a Template. Below are steps you might want to do to prepare an existing workbook to be saved as a Template:

  1. Remove the data from the cells with Constant Numbers:
    GoTo (Ctrl + G or F5 key)
    Click Special Select Constants and remove the check mark in front of Text.
    Click OK. 
    Press the Delete key on the keyboard
  2. Conditionally Format the selected cells to make it clear which cells will need data:
    NOTE:  Do NOT click in any cells in the worksheet or the selected cells will no longer be selected!
    Click on Conditional Formatting in the Styles section of the HOME Ribbon
    conditional-formating-new-rule
    Click on New Rule
    Select Format only cells that contain
    Either change Cell Value to Blank or change between to equal to then type =”” (=QuoteQuote with no spaces) in the line at the right
    conditional-format-equal-to-equal-to-quote-quote
    Click Format and on the Fill tab, select any color of your choice (I won’t matter the color because the color will be gone once the cell has data in it)
    Click OK twice
  3. Click in the 1st cell you need to type in
  4. Excel 2007 – Click the Microsoft Logo in the upper right of Excel
    Excel 2010 + – Click the File tab
  5. Select Save As
  6. Change the Save as type: to Excel Template
  7. Enter an appropriate name for the template
  8. Check to see if Excel automatically selected the Template folder for you to save the template into.
    template-folder-for-templates
    Excel 2013 will create a folder in Documents called Custom Office Templates and save the workbook there.
    template-folder-for-2013-excel-templates
  9. Click Save and then close the workbook.

Excel 2013’s Start Screen March 23, 2016

Posted by computertrainer in Excel Tips, Office 2013.
Tags: , ,
2 comments

If you are like me, you will prefer that Excel 2013 opens to a blank new workbook, not the start screen:

2013 Start page original

Steps to make the blank new workbook your opening screen:

  1. Double-click on Blank workbook from the start screen.
  2. Click the File Tab
  3. Select Options at the bottom of the list on the left
  4. In the General category, at the bottom of the screen, remove the check in front of “Show the Start screen when this application starts
    excel 2013 Options General START
  5. Click OK.

Insert an Empty Row Between Each Row of Data in Excel February 14, 2016

Posted by computertrainer in Excel Tips, Office 2007 Tips, Office 2010 Tips, Office 2013, Uncategorized.
Tags: ,
add a comment

My daughter phoned one day to ask how she could add an empty row between each row of data in Excel.  This is a quick and easy solution: 

  1. Insert a column to the left of Column A
  2. In the empty column enter “1” in the row with the 1st row of data
  3. AutoFill the number down
    Insert an Empty Row 1
  4. At the bottom of the screen by the Column A, click on the AutoFill options box and select Fill Series
  5. The series is selected from 1 to whatever the last row would be numbered. Copy that selected series.
  6. Press Ctrl + down arrow to go to the bottom of the data and then go down 1 cell to the 1st empty cell below the series and Paste.
    Insert an Empty Row 2
  7. You now have numbered the data from 1 to x and 1 to x.
  8. Sort the data by column A and then by column B
    Insert an Empty Row 3

Tada!  Every other row is blank.

HINT:  Do not delete column A yet.  Perhaps you would use the Group feature to hide it.  When you want to resort so you do not have every other row empty, column A will be the “bridge” you need to sort all the data.  Simply sort by any column other than column A.

Use Voting Buttons in Outlook December 6, 2015

Posted by computertrainer in Office 2007 Tips, Office 2010 Tips, Office 2013, Outlook Tips.
Tags: , , ,
add a comment

It’s easy to create a poll in Outlook by including voting buttons in an email message.  The poll responses arrive in your Inbox and are tallied in a tracking view.

NOTE:  Microsoft Exchange Server is required!

Follow the steps below to send a message with voting buttons:

  1. First, open a new email message from within Outlook.
  2. Click the Options tab on the Ribbon.
  3. In the Tracking group, click on Use Voting Buttons. A drop-down menu will appear offering you four options: Approve/Reject; Yes/No; Yes/No/Maybe; or Custom…
    • If you select Custom, type in the list of options that you want the recipients to be able to choose from, separated with a semi-colon (;).
  4. Your untitled message form appears with a note at the top that tells you that you have added voting buttons to this message. You can now continue to create your message.
  5. Fill in the To… field with the recipients’ email addresses.
  6. In the Subject field, type your question (i.e., Where would you like to meet for our lunch meeting?)
  7. Type in any other relevant information that you want to include in the body of the email. If this is the first time you have sent an email that contains voting buttons to these recipients, you may want to include a note that gives directions on how to reply.
    vote e-mail
  8. When the message is complete, click on the Send button.

Receive a message with a voting request:

When you receive an email message that contains voting buttons, you can quickly respond to the sender’s request for a vote.

To vote, do one of the following:

  1. In the Reading Pane, click the InfoBar, and then click your choice.
  2. Open the message, and click the Message tab. In the Respond group, click Vote, and then click your choice.If the recipient clicks on the first radio button, the vote will be sent without any added message. If the recipient clicks on the second option, a new message form will appear and the recipient will be able to type in an explanation or additional information that will accompany their response.
  3. You are prompted to choose whether you want to include a message together with your vote before the response is sent.
    vote response

Review voting responses:

Regardless of which option is chosen, the subject header of the reply message will include the recipient’s vote followed by a colon and then the subject line from the original email.  This makes it easy for the sender to manually count the votes, since the responses will show up as new items in the sender’s Inbox as the recipients reply.

vote returned msg

When you send out messages where recipients must vote, you can quickly count the results by clicking on the informational message at the top of the window that tells you how the sender responded.  A pop up will appear that says View voting responses.

vote - view voting responses

When you click on View voting responses, a new window will appear and you will be able to see a running tally of the total responses that have been sent.  Beneath that, there will be a list of each recipient and their individual response.

vote - responses details

You can also open the original message, click on the Message tab, in the Show group, click Tracking.

Need more screen space for your Excel Dashboard? October 13, 2015

Posted by computertrainer in Excel Tips, Office 2010 Tips, Office 2013, Dashboard Tips.
Tags: , , , ,
add a comment

With just a few setting changes, you can open the screen on your monitor to allow for more space for your Excel Dashboard:

On the View Ribbon, in the Show section, remove the check mark from:

  • Gridlines
  • Formula Bar
  • Headings

Click the File tab, then Options.  On the left select Advanced.  Scroll to the section “Display options for this workbook”.  Remove the check mark from:

  • Show horizontal scroll bar
  • Show vertical scroll bar
  • Show sheet tabs  [NOTE:  if you need to move to another sheet, perhaps you have several worksheets with different dashboards, use CTRL + PgDn to move to the worksheet to the right and CTRL + PgUp to move to the worksheet to the left.]

Collapse the Ribbons by double clicking on a Ribbon tab.

You now have an uncluttered screen to display your Dashboard!

Excel PivotTable Issue: Create a Calculated Field that has a Field Title that uses Alt + Enter to Split the Field Title into Multiple Lines June 21, 2015

Posted by computertrainer in Excel Tips, Office 2003 Tips, Office 2007 Tips, Office 2010 Tips, Office 2013.
Tags: , ,
add a comment

One of the rules for working with an Excel database and preparing it for a PivotTable is that the raw list of worksheet data must contain a header (individual column headings) row and that row can only be one row deep.

NOT this!

PivotTable Rule 1 - 1

 

Below is a workbook with titles on one row but  that makes the columns too wide:

PivotTable Rule 1 - 2

Below the title row is set to wrap text and the columns, when sized to fit, are narrower but do not break where I want:

PivotTable Rule 1 - 3

Use Alt + Enter to split the titles into multiple lines within the cell.  It this example, click before 1st and Press ALT + Enter.  Repeat for 2nd, 3rd, 4th, and Year:

 PivotTable Rule 1 - 4

Now for the Issue of using ALT + Enter in a Field title:

I am creating a PivotTable with the above data (not the ideal format/layout but it will work) and want to add a Calculated Field that gives me the Commission for the Total Year based on a 25% commission rate.

The field titles have all been split by using Alt + Enter.  Notice in the Formula: for the Insert Calculated Field dialog box (below) that I only see =‘Sales.  The remaining part of the field name is out of sight.

PivotTable Alt + Enter Issue in Calculated Field

Solutions:

  • At the end of the field you see in the Formula section, press and hold the right arrow until it stops moving.  It MIGHT show a small flashing dot where the field name ends on the line below what you can see.  At that point, I could type the balance of the formula BUT I would have to TRUST that I entered the correct information because I will not see it.
  • Cancel the Insert Calculated Field process and return to the data and remove the Alt + Enter by expanding the Formula Bar, click at the start of the 2nd line and press Backspace.  The Field title is now on one line.  (See below)  You will need to Refresh the PivotTable and, perhaps, add the field back to the PivotTable.

PivotTable Alt + Enter Issue in Calculated Field cleanup1     PivotTable Alt + Enter Issue in Calculated Field cleanup2

 Issue Solved:

PivotTable Alt + Enter Issue in Calculated Field cleanup3

 

 

Thank you to DB, DH, LW, & NW for pointing out this issue during our PivotTable class.

Display the Ampersand (&) in an Access Database Label or Text Box June 21, 2015

Posted by computertrainer in Access Tips, Office 2003 Tips, Office 2007 Tips, Office 2010 Tips, Office 2013.
Tags:
add a comment

It you need to use the ampersand (&) in a label or text box in a Microsoft Access database, it will display as an underscore (_).

For example, you type and want to display “Sales & Marketing” but you get “Sales _ Marketing”.

To display the & symbol:

  • Double up the & – “Sales && Marketing”.  The result will be “Sales & Marketing

This is for you – Don, Chris, Marlana, Paul, Dan, & Noah!

%d bloggers like this: