People With Excel Skills Earn More Money!

excel_2013Looking to learn Excel or brush up on your existing skills?
Our basic and advanced Excel classes are on sale for $199 (normally $299)!

The price includes the following:
-One full day training in the subject area (7 hrs with a 1 hr lunch included) or 2 or evenings (3 hrs each session)
-Free Retakes for Life!”™ of that version
-Any Upgraded version of the class taken offered at 75% off, with free retakes applied to that class/version
-Manual to keep w/ access to student files
-Free forum support for after class
-Mousepad withshortcuts to keep
-Hands on, project-based learning
-Small Classes of 4 to 9 people
-Fast NEW computers!
-Comfortable chairs, convenient location
-Patient, Fun, Experienced Trainers
-We provide coffee, tea, water and chocolate

Visit our website at www.training-nyc.com where you can view all our classes and purchase a seat 24/7.

We also offer private, one-on-one training with our Excel experts.

To arrange a private session, please call 212-658-1918 or email karyn@training-nyc.com

NYIM Training — ‘the most practical computer training in NYC”

Excel for Mac Level 1 class ON SALE for $149

Excel for Mac

Excel for Mac Level 1 class on Saturday, September 3rd, 2016 is on sale for $149! (reduced from $199).

We offer the most practical Excel group classes and private training in NYC.

Excel for Mac group class concentrates on the most common topics we see NYC businesses using on a daily basis. By the end of the Excel training course, you will feel confident managing Excel lists, databases, charts, common formulas, formatting, shortcuts and printing. This Excel class is perfect for those who are new to Excel, have been using Excel without any training or need to gain a solid grasp of how to use it effectively. We currently use Excel 2011 for Mac.
Visit us at www.training-nyc.com or call 212-658-1918 to enroll while the price is hot!

Excel for Business Level 1 class ON SALE $149

Our Excel for Business Level 1 class on August 27, 2016 is on sale for $149! (reduced from $199).

We offer the most practical Excel group classes and private training in NYC.

The Excel for Business group class concentrates on the most common tasks that we see NYC businesses using on a daily basis.

Visit us at www.training-nyc.com or call 212-658-1918 to enroll while the price is hot!

How to Multiple PivotTables with Different Month and Day Groupings from the same Data Source [ANSWER]

How to create Multiple PivotTables with Different Month and Day Groupings from the same Data Source

When multiple Pivot Tables are created it initiates the OnLine Analytical Processing by default. Basically this translates to a smaller file size and caching. There is a way to create Pivot Tables from the same source but not have the Analytical Processing on.

The problem is when you need to have a field grouped one way (For example in Months, and a different PivotTable grouped in Days) it is not possible.

Two separate PivotTables may be created, in Excel 2013 for example, with separate caches driven from the same raw data with use of the PivotTable and PivotChart Wizard (Alt+D, P). The second (and any subsequent) time around, before finishing “Step 2 of 3” click Next and the following message box should appear:

SO25731981 example

Be sure to select No and be warned that this may significantly increase the size of your file.

 

The other option is to create grouping columns in your raw data. For example:

=TEXT(B4,”mmm”)
This will turn a date into a short Month Name. Ex. Jan

=TEXT(B4,”mmmm”)
This will turn a date into a long Month Name. Ex. January

 

Multiple PivotTables with Different Month and Day Groupings from the same Data Source

Excel VBA Macro to Close a Workbook without Saving and Reopen

Excel VBA Macro to Close a Workbook without Saving and Reopen

I wrote this macro and use it all the time when writing new macros. Feel free to use.

 

Sub ClosewithoutsaveAndReopen()
‘Defines Variables
Dim nameOfCurrentWorkbook As Variant
Dim nameOfCurrentWorkbookPath As Variant

‘Finds the name of the current workbook
nameOfCurrentWorkbook = ActiveWorkbook.Name

‘finds the current path of hte workbook
nameOfCurrentWorkbookPath = ActiveWorkbook.Path

ActiveWorkbook.Close False
‘Reopens the same worbook you just closed
‘If in the same workbook
Workbooks.Open (nameOfCurrentWorkbookPath & “\” & nameOfCurrentWorkbook)
‘if in a specific workbook
‘Workbooks.Open (“C:\Users\VMWindows7\Desktop\SAS CARB 2012_44.xlsx”)
End Sub

 

 

Excel VBA Macro to Close a Workbook without Saving and Reopen

The Clipboard cannot be emptied. Another program might be using the Clipboard [ANSWERED]

The Problem is Skype Click to Call Button.

cliboarded empty

Skype Click to Call add-on that is installed in your browser when you install Skype. Skype released a new version of this add-on back in April that resolves this issue with Excel.

The following sticky goes into more detail about how to get the latest version of Skype’s Click to Call:

Text loses formatting when I use Cut, Copy, Paste, Paste Special or Format Painter in Excel

How to Freeze Panes in Excel [Solved]

How to Freeze Panes in Excel

Excel LogoIf you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

 

Freeze Top Row

To freeze the top row, execute the following steps.

1. On the View tab, click Freeze Panes, Freeze Top Row.

Freeze Top Row

2. Scroll down to the rest of the worksheet.

Result. Excel automatically adds a black horizontal line to indicate that the top row is frozen.

Freeze Top Row Result

Note: to keep the first column visible while scrolling through the right of the worksheet, click Freeze First Column.

Unfreeze Panes

To unlock all rows and columns, execute the following steps.

1. On the View tab, click Freeze Panes, Unfreeze Panes.

Unfreeze Panes

Freeze Panes

To freeze panes, execute the following steps.

1. Select row 3.

2. On the View tab, click Freeze Panes, Freeze Panes.

Freeze Rows

3. Scroll down to the rest of the worksheet.

Result. All rows above row 3 are frozen.

Freeze Rows Result

Note: to keep columns visible while scrolling to the right of the worksheet, select a column and click Freeze panes.

4. Select cell C3 (unfreeze panes first).

5. On the View tab, click Freeze Panes, Freeze Panes.

Result. The region above row 3 and to the left of column C is frozen.

Freeze Rows and Columns Result

Credit: http://www.excel-easy.com/examples/freeze-panes.html

How to Merge Cells in Excel

How to Merge Cells in Excel

Excel LogoThis document explains how to merge cells within all versions of Microsoft Excel.

Merging cells is often used when a title is to be centered over a particular section of a spreadsheet. When a group of cells is merged, only the text in the upper-leftmost box is preserved.

To merge a group of cells:

  1. Highlight or select a range of cells.
  2. Right-click on the highlighted cells and select Format Cells….
  3. Click the Alignment tab and place a checkmark in the checkbox labeled Merge cells.

To merge a group of cells and center the text, you can also use the Merge and Center button on the Excel tool bar. Again, this will only preserve the text in the upper-leftmost cell.

  1. Highlight or select a range of cells.

Click the Merge and Center button on the toolbar.

Excel XP and Excel 2003
Excel XP and 2003 Merge and Center button

Excel 2007
Excel 2007 Merge and Center button

Excel 2008 (Mac)

Excel 2008 Merge and Center button

Excel 2010

Excel 2010 Merge and Center button

Excel 2011 (Mac)

Excel 2011 Mac Merge and Center button

Excel 2013
Excel 2013 Merge and Center button

Credit: https://kb.wisc.edu/helpdesk/page.php?id=939

How to Use Quick Sort Button in Excel [Solved]

How to Use Quick Sort Button in Excel

Excel LogoIn Excel,you can quickly sort your data by using the A-Z and Z-A Sort buttons on the Ribbon’s Data tab. But, be careful, or one column may be sorted, while others are not.

Only use this technique if there are no blank rows or columns within the data.

    1. Select one cell in the column you want to sort.
    2. On the Excel Ribbon, click the Data tab.
    3. Click Sort A to Z (smallest to largest) or Sort Z to A (largest to smallest)

data tab sort a-z

  1. Before you do anything else, check the data, to ensure that the rows have sorted correctly. If things look wrong, immediately click the Undo button on the toolbar.

Problems with Sorting Excel Data

When you quickly sort data with the A-Z or Z-A button, things can go horribly wrong. If there is a blank row or blank columns within the data, part of the data might be sorted, while other data is ignored. Imagine the mess you’ll have, if names and phone number no longer match, or if orders go to the wrong customers!

Follow these steps to help prevent problems when sorting Excel data:

    1. Select one cell in the column you want to sort.
    2. Press Ctrl + A, to select the entire region.
    3. Check the selected area, to make sure that all the data is included. For example, in the screen shot below, hidden column E is blank, so columns at the left are not selected.

sort hidden column

  1. If all the data was not selected, fix any blank columns or rows, and try again. Or, use the Sort Dialog box, as described in the next section.
  2. If all the data is selected, click Sort A to Z (smallest to largest) or Sort Z to A (largest to smallest)
  3. Before you do anything else, check the data, to ensure that the rows have sorted correctly. If things look wrong, click the Undo button on the toolbar.

Sort Two or More Columns

If you want to sort 2 or more columns in an Excel table, you can use the Sort dialog box. In this example, we’ll sort a table with personal data. First, the data will be sorted by Gender, then by State, and then by Birth Year.

    1. Select all the cells in the list.
      This is the safest approach to sorting. In most cases, you can select one cell and Excel will correctly detect the rest of the list — but it’s not 100% certain. Some of the data may be missed.
    2. On the Excel Ribbon, click the Data tab.
    3. In the Sort & Filter group, click the Sort button.

data tab sort

    1. Click the Add Level button, to add the first sorting level.
    2. From the Sort by dropdown, select the first column you want to sort. In this example, Gender will be the first column sorted.

sort by drop down

Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers.

my data has headers

    1. From the Sort On drop down, select the option that you want. We’re sorting on the values in the Gender column, so leave the default setting of Values.

sort on values

    1. Next, from the Order drop down, select one of the options. The list of Order options will depend on what you selected in the Sort On column. Because we selected values, the Order options are A to Z, Z to A and Custom List. We’ll select A to Z.

sort order options

  1. If you are sorting on multiple columns, click the Add Level button, to add the next level, and select options from its drop down boxes.Here we have selected Gender, State and BirthYr as the sort fields, and all are sorted on Values. Because the BirthYr column contains only numbers, its Order options are slightly different from the text column options.sort order numbers
  2. After you have selected all the Sort levels, and their options, click OK.

The data will be sorted in the order that you specified. In the screen shot below:

  • Gender column is sorted first, so all the female names are at the top.
  • Next, the State column is sorted, so females from Alabama are at the top of the list.
  • Finally, the BirthYr is sorted, with the earliest birth years at the top of each state.list sorted by 3 columns

Credit: http://www.contextures.com/xlSort01.html

How to Force a Page Break in Excel [Solved]

How to Force a Page Break in Excel

Excel LogoAs your worksheet gets larger, there may be times when you want to force Excel to starting printing on a new page. For instance, you might want only the first fifteen rows of information on the first page, then the balance of the worksheet on the second printout page. To make this happen, follow these steps:

  1. Make sure cell A16 is selected.
  2. Choose Page Break from the Insert menu. A dashed line appears between rows 15 and 16.
  3. Print your worksheet as normal.

If you want to later remove the page break, follow these steps:

  1. Make sure cell A16 is selected.
  2. Choose Remove Page Break from the Insert menu. The dashed line disappears.

Credit: http://excel.tips.net/T001930_Forcing_a_Page_Break.html