jump to navigation

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 Dashboard Tips, Excel Tips, Office 2010 Tips, Office 2013.
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!

Use the Tab Scroll Buttons in Excel June 21, 2015

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

To make a specific worksheet active for editing, you can click on its sheet tab. Excel lets you know what sheet is active by displaying the sheet name in boldface and making its tab appear on top of the others.  The active sheet tab is a different color from the inactive sheet tabs.

On the bottom left of the worksheet you will find the Sheet Tab scroll buttons – First sheet, Previous sheet, Next sheet, and Last sheet.  They are followed by the tabs for the worksheets in your workbook and the Insert Worksheet button or tab, depending on the version of Excel you are using. If your workbook contains too many sheets for all their tabs to be displayed at the bottom of the worksheet area, use the Sheet Tab scroll buttons to bring new tabs into view (so that you can then click them to activate them).

If you prefer, right click on one of the Sheet Tab scroll buttons and a list of the sheets will appear.  If there are more sheets than can be displayed, More Sheets …. appear at the bottom of the list.  When you click on a sheet name, you will be moved to that sheet and it will be the active sheet.

You also can use the Ctrl+Page Down and Ctrl+Page Up keyboard shortcuts to activate the next and previous sheet, respectively, in your workbook.

Follow

Get every new post delivered to your Inbox.

Join 98 other followers

%d bloggers like this: