Print Macro

I don't know about you but I am tired of always having to change my spreadsheet to a landscape orientation before printing. Personally, I think Microsoft should have made the default oreientation landscape. Anyway, I have been trying to play around and use macros so I decided to just write a macro to do it for me. This is a simple macro - all it does is change the print orientation to Landscape.

The green is just information for the user and is not part of the actual macro.
To access the Developer tab where all the macro features exist, in Excel 2010, you need to:

• Click on File>Options
• Select Customize Ribbon
• Select Developer
• Click OK

Once you have  the Developer tab visible,

• Copy the code below
• Click on the Visual Basic icon (or press Alt+F11)
• Click Insert>Module
• A blank window will display
• Click Edit>Paste
• You can run it to test it or debug it if you wish
• Click File>Close and Return to Excel
• Click the Macros icon
• From there you can edit it, run it, or click on Options to create a shortcut key.

If you record the steps yoursefl, your code will be a lot longer but it will still work. You could also add in whatever other print features you change all the time. I gave mine a shortcut key of Control+Shift+P but you can give it a key combination that makes sense to you.

Sub Landscape_Printing()

'
' Landscape_Printing Macro
' This macro changes the print setting to landscape.
'
' Application.PrintCommunication = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With
Application.PrintCommunication = True
End Sub

 

Pick From A List

If you are constantly typing something such as a list of employee names or a list of cost center codes consider using the Pick From Drop-Down List. It is very easy to use.

Type your data. In the example below, I typed a series of names...

When I wanted to repeat one that I had previously typed, all I had to do was right-click and select Pick From Drop-Down List. The Drop-Down List displays all the names that I had typed above.

Excel then displays the list of names I had previously typed in alphabetical order. Now I can just click on the one I want and Excel will enter it for me. So, it saves a bit of typing.

And no, I'm sorry- I know what you are thinking  but unfortunately it only works with text.

Data Validation, another feature in Excel, also allows you create a list and is a bit more flexible although this Pick From Drop-Down list is a bit quicker.

USE THE OFFSET FUNCTION TO CREATE A HORIZONTAL CHART THAT AUTOMATICALLY UPDATES

In most cases people tend to add data to a chart vertically however there are instances where you need to add columns of data to your chart. To have the chart automatically pick up additional columns as they are added you should use the OFFSET function.
In the example below I have widget sales for January through March.

 

 
 
 
I wanted to create a chart so that as I added April through December sales into the chart, the chart would automatically include the new data without me having to do anything to the chart.

 

First, I need to create my chart as I normally would. I am using a column chart as i think it is a little easier to see.

 

 

 

 

 
 
 
 
 
 
 
 
Then I need to identify each of the 2 rows to Excel by creating defined names.
We are going to create a name and formula for the months and then for the widget sales.
Go to the Formula tab and select Define Name

• 
   
• In the Name box type Month_Labels
• In the Refers To box: type =OFFSET(Sheet1!$A$1,,1,,COUNTA(Sheet1!$B$1:$ZZ$1))
• Click OK

This is telling Excel to go to cell A1 and then move over 1 column and to start selecting data all the way to Column ZZ.  Now currently there is only data Columns A through D but as we add additional columns of data, Excel will be looking for it.
Now we need to define the widget sales

• Go to the Formula tab and select Define Name
• In the Name box type Widget_Data
• In the Refers To box: type =OFFSET(Sheet1!$A$2,,1,1,COUNTA(Sheet1!$B$2:$ZZ$2))
• Click OK

This is telling Excel to go to cell A2 and to move over 1 column and select data from B2 through Column ZZ.

 

 

 

Click in the chart and select the data series and replace the cell references to the formula names of Month_Labels and Widget_Data

Now as you add April and May's data, Excel will automatically include it in the chart.

Click here to download the Excel file.

Apps and More

I have really gotten attached to my IPAD.  It has some really neat apps and I thought I would share a few before I get back to chatting about Excel.

If you are buying the IPAD as a reader, I have to tell you that in my opinion, ibooks is terrible. The book selection is poor. The good news is that you can download for free the Kindle App. You can buy your books from Amazon and with a click it downloads it to your IPAD. Amazon as I am sure everyone knows has a broad spectrum of EBooks.

If you are looking for news- there is tons of stuff. NPR News, CNBC, CNN, NYTimes, Teh New Yorker and the WSJ  all have apps so you can read their material when you are in a wifi area.

Flipboard is an absoutely cool app as it set up in  a magazine format and you can flip through the news, facebook updates, magazines that it allows you to select from. You can mix your social media, images and news all in one place. And it is FREE.

I am not a huge game player but a couple of the word games have me hooked. These are all free by the way.
My absolute favorite is Iassociate.
iAssociate is a word association game, where the goal of the game is to guess which words, or phrases, are associated to the other words in the game. Every word is associated to at least two other words, and the associations are meant to be things that you come to think of upon hearing the word. The first level is easy and fun and then it just gets harder and more frustrating as the levels go up but I am hooked- I have made it through almost all of the free levels and have actually bought the next level. This is totally addicting!

Fling is an absolutely cool game that my daughter introduced me to awhile back and I used to play on my ipod touch. You fling colored fur balls around the screen. You have to get them off the screen by bouncing them off one another. The first level or two is a yawn and then it gets interesting.  You really have to strategize.

Three more traditional games are:
Whirly World presents you with a a wheel containing 6 letters. It is your job to come up with as many word combinations as possible in a minute. 

Scrambe CE is similar to boggle. You have 16 letters and you have to drag or tap the letters to form words. You can play it solo or online.

Words with Friends is playing scrabble with  a friend. You can play with someone you know or pick someone randomly that is online searching for a partner. This game can be frustrating as you have to wait sometimes for a couple of hours or a day to finish a game depending on how quick your opponent is. This game requires wi-fi to play.

Now you know what I do with my free time!

There are a lot of games and other apps out there so check them out if you have an IPAD or Iphone. They can be particularly handy if you sitting in an airport as they all work offline -except for Words with Friends.
These are just my opinions but since everything is free - it is easy to check them out for yourselves.
Have a great Labor Day and next next week I will get back to blogging about Excel.

Patricia

IPAD Tip- Organizing Your Apps

Hi All
I was at the Maroon 5 &Train concert last night. It took place at Conseco Fieldhouse instead of at the State Fair grounds due to the Fair stage collapse earlier this week. It was an absolutely  fantastic show but what was truly impressive was the  fact that every single thing was donated- the FieldHouse, the band's performances and most impressive - all the workers. Every person working there including the ushers, stage hands and concession workers donated their time so that all funds could go to the Indiana State Fair Remembrance Fund. Hoosiers are a truly phenomal group of people.

If you are like me and have screen after screen of IPAD apps, consider organizing them. It is very easy-simply select an app
(press on it until it starts to shimmy)and then drag it on top of another app and the IPAD will create and put the apps in a box that you can name. Then just keep dragging apps into it. (To get it to stop shimmying- press the Power button once).
I now have a nice box of  Card Games and another nice box of Word Strategy Games.  Next week I will share 2 or 3 of my favorite game apps that you might want to consider downloading for your "free time" and then I will get back to Excel tips.
I hope that you have all had time to check out my new site Excel-Diva.com. We will be adding more materials to it and hopefully a few more videos shortly.

Have a great Friday and an even better weekend.