Excel 2013’s Start Screen March 23, 2016Posted by computertrainer in Excel Tips, Office 2013.
Tags: Excel 2013 Start, Excel 2013 Start Screen, Start screen
If you are like me, you will prefer that Excel 2013 opens to a blank new workbook, not the start screen:
Steps to make the blank new workbook your opening screen:
Insert an Empty Row Between Each Row of Data in Excel February 14, 2016Posted by computertrainer in Excel Tips, Office 2007 Tips, Office 2010 Tips, Office 2013, Uncategorized.
Tags: Everyother Row Empty Excel, Excel Insert Empty Rows
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:
- Insert a column to the left of Column A
- In the empty column enter “1” in the row with the 1st row of data
- AutoFill the number down
- At the bottom of the screen by the Column A, click on the AutoFill options box and select Fill Series
- The series is selected from 1 to whatever the last row would be numbered. Copy that selected series.
- 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.
- You now have numbered the data from 1 to x and 1 to x.
- Sort the data by column A and then by column B
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.
Need more screen space for your Excel Dashboard? October 13, 2015Posted by computertrainer in Dashboard Tips, Excel Tips, Office 2010 Tips, Office 2013.
Tags: Declutter Excel screen for Dashboard, Excel Dashboard, Excel Window, More space on screen for Dashboard, Open up Excel screen
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:
- Formula Bar
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, 2015Posted by computertrainer in Excel Tips, Office 2003 Tips, Office 2007 Tips, Office 2010 Tips, Office 2013.
Tags: Alt+Enter in a Field name, Calculated Field in PivotTable, Excel PivotTable
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.
Below is a workbook with titles on one row but that makes the columns too wide:
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:
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:
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.
- 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.
Thank you to DB, DH, LW, & NW for pointing out this issue during our PivotTable class.
Tags: Display Ampersand not underscore
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, 2015Posted by computertrainer in Excel Tips, Office 2003 Tips, Office 2007 Tips, Office 2010 Tips, Office 2013.
Tags: Excel sheet tabs, Sheet Tabs
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.