It has been a while...

It has been a while since I have posted, sorry...

Does anyone have any ideas as to what I should post...

Please let me know at shawnrblogs@gmail.com

Thanks,

Shawn

Clip Art

Now it is Time to have Fun with Excel

Since you have worked so hard with the Basic Concepts in Excel I figured that it is time to show you some of the fun side of Excel.

Yes it is true you can have fun…

Working with Clip Art

Most computers have Clip Art built into the programs such as Microsoft Office, however, there are a few companies and the military and government organizations that like to save space by not adding the Clip Art portion of the program in. So if you are using a computer supplied by one of the above mentioned you will be able to read the steps but you will not be able to work through them.

I know that I am not going in order of the buttons in the Illustrations Group on the Insert Tab.

Clip Art

What makes Clip Art different from other images such as Pictures?

Clip Art basically are the images that look more cartoonish, hand drawn, and charactertures, you know when the artist has drawn a person and their head is too big for their body.

If you have not already done so go ahead and open an Excel Workbook. Then go to the Insert Tab on the Ribbon, there you will find a Group called Illustrations. You should see four buttons that you can choose from.
1. Picture – you can go anywhere on your system or network to find Pictures and import them into Excel.

2. Clip Art – this should bring up a selection of Clip Art that you can choose from. There are places that you can go to online and find many more collections of Clip Art. Be careful doing this because there are some web-sites that have viruses on them.

3. Shapes – you can choose from a large selection of shapes if you desire to create something for yourself. This can be done by using multiple shapes and arranging them how you want. Latter I will show you how to make a quick robot with Shapes.

4. SmartArt – this is a cool addition to the Excel program, you used to have to build these from scratch, or the ground up. I have a complete lesson on this latter in the Intermediate Concepts Section.

The Clip Art Button

When you click the Clip Art Button you will get a Dialog Box to open up. The Clip Art Dialog Box will have five sections to it.

1. Search For – if you have a specific item that you are looking for you can type it in the Search For Text Area then click the Go Button. In the Large white area below it will show you what if found that matched your criteria.

2. Search In – This section has a list of different places that you can find Clip Art. If it does not say All Collections use the drop down arrow and make sure that all the boxes are checked.

3. Results Should Be – here you can tell Excel what you want it to search for. Although the Photographs, Movies, and Sounds are not technically clip art.
     a. Clip Art
     b. Photographs
     c. Movies
     d. Sounds

4. The View Area – this is where the results of the search will appear.

5. The Three Buttons in the bottom
     a. Organize clips – here is where you can go if you have downloaded Clip Art from the internet, you can                  add them to the folder that you wish them to be in or you can create your own folders.
     b. Clip art on Office Online – this will take you to the Microsoft Office Online site where you can pick and choose from their selection of Clip Art.
     c. Tips for finding clips – this will take you to the Excel Help Screen.

Inserting Clip Art into your Spreadsheet

There are three ways that you can insert Clip Art into your Spreadsheet. After you have searched for the Clip Art that you wish to add;

1. The Double Click Method – you can click the Left Mouse Button twice and the Clip Art that you Double Clicked on will be placed into your Spreadsheet.

2. Drag and Drop – you can hover over the Clip Art that you wish to insert into your Spreadsheet and Hold the Left Mouse Button down and drag the Clip Art to where you wish to place it and let go.

3. The Drop Down Arrow – as you hover over the Clip Art that you want to insert into your Spreadsheet you will see a grey button appear down the right hand side of the Clip Art sample. If you Click on the Button you will get a Dialog Box that allows you to choose from several choices. When this Dialog Box appears you can choose Insert and it will put the selected Clip Art into your Spreadsheet.

4. Okay, so I misled you… There are actually four ways to add Clip Art, if you get the Dialog Box you can choose Copy and then Paste it into the Spreadsheet.

Suggestions

I would like to take this opportunity to ask the readers what they would like to see posted in this blog.

Please tell me what version of MS Office that you use and what program, Excel, Word, PowerPoint, etc.

Also, if you could ask questions or make comments about what you would like to see in an instructional book, what would it be.

Let me know, you can either leave a comment or email me at shawnrblogs@gmail.com

Thank you.

The Easiest Way to Create a Chart

     Have someone else do it...

     Just kidding.

The Easiest way to Create a Chart

Okay, are you ready for the Easiest way of Creating a Chart?

Well, I hope that you are because I am going to give it to you even if you do not want it.

The First thing that you need to do is to select the Data Range for your Chart. I would like you to select the Range of Cells from A5 to G13.

No w that that is done.

Now, you have two hands, Right?

Ten fingers or perhaps you may say no I have eight fingers and two thumbs.

Hold your hands up, either hand I want you to choose one finger, not the third finger over from either direction…

Some people get that joke right away, makes me wonder do they use that finger a lot…

There is the Functions Button Row on your Keyboard, the F1, F2, F3, F4, all the way to F12. It is usually the upper most row of Buttons on the Keyboard. They are actually good for something other than catching dust. Well I want you to use the finger that you chose and click the F11 Button.

Ka-pow…

Two things should have happened simultaneously Excel should have created a Tab Called Chart1 and it also created the Chart.

Did I lie, was that not the easiest way that you created a Chart?

Okay, I would like for you to go back to the Charts Tab and this time Select Cells A5 to G5 and Cells A15 to G15. It should look something like the below picture. Then use the F11 Key again.

You should get a Second Chart on a Chart2 Tab. Now you have the option of showing the Boss both charts in an uncluttered Spreadsheet. Or you could have something that looks like this. Which looks best to you, the three Tabs or the One Tab with all the information on it?

What choice is the easiest to read, especially for the audience?

Charts the Easier Way

     NOTE: The Contextual Tabs are called Contextual because they appear in context of what you are dealing with. So with Charts you will get the Design, Layout, and Format Tabs that deal with Charts. If you are working with Pictures then you will get the Contextual Tab that deals woth Pictures.
   
     The Easy to Create a Chart

     If you want to create a Chart that has the Total, select Cells A5 to G5, than hold your CTRL Key down and select Cells A15 to G15. Then go to the Insert Tab and click the Column Button in the Charts Group. Once the Dialog Box appears click the 2-D Clustered Column Chart, or the first one in line and you will get the same Chart that you had before. With the Chart Selected you will get the Contextual Tabs once again.

Your Chart should look like this.

     Now then go ahead and Delete this Chart. I know, it is not fair, I keep having you make the Charts and then Delete them, well practice makes perfect. As they say, but who are they, I have always wondered.

     This time I am going to have you create a Chart with different information in it. Select the Cell Range A5 to G13, you can do this a couple of different ways. One way is to Click into Cell A5 and then hold your Left Mouse Button down and drag to Cell G13 and then let go. Another way to do this is to Click into Cell A5 and then hold your Shift Key Down and Click into Cell G13, then release your Shift Key.
Now, you know where to go and what to do. Hey, I mean go to the Insert Tab, what were you thinking…

     Once you get to the Insert Tab use the Column Button and choose the First Button in the 2D Column List called Clustered Column. Now your Chart should look like this.

     This time you have a list of Items in the Legends area, the Category or Horizontal Value is not the Product Name, the Values are still in Dollar Format like the Totals. This time, however, you have several different Colors for the Months instead of just one Color as with the Totals. This way it is easier to keep the Product Totals for the Months separated and quickly readable.

     I bet you know what I am going to ask you to do now, please Delete the Chart. Because it is almost time to learn the quickest way to create a chart that I know of.

Charts the Hard Way

To follow the steps in this you will need to download this file Basic Concepts it has been designed for Excel 2007

The Hard way to Create a Chart

                With the Charts Spreadsheet being the active sheet go to the Insert Tab and there is a group called Charts.  This Group has several kinds of Charts that you can choose from.  There are Column Charts, Line Graphs, Pie Charts, Bar Charts, Area Charts, Scatter Charts, and Other Charts.  The Most common and universal Chart is the Column Chart.  This is the one that you will be spending most of your time working with.

                When you click the Column Button a Dialog Box will open and it has several different styles of Column Chart that you can use.  The most common Chart in this Dialog is the 2-D Clustered Column Chart, or the first one in line.

                Click on the 2-D Left most Button and two things will happen.  The First thing that most people notice is that there is now a blank box covering part of your data.  The second thing, sometimes this needs pointed out before people notice, is that there are now three more Tabs on the Ribbon.  They are Design, Layout, and Format.

                You can move the Empty Box around on the Spreadsheet so that you can see all of you data.  As you hover over the Blue Surround of the Box you will see the Crossed Arrows, when you get this symbol hold your Left Mouse Button down and move the Box to where you want it.  There are also Eight sets of three dots, one in each corner, and one in the middle off each of the runs, these can be used to make the Chart larger or smaller.  As you hover over the three dots your cursor will change from the crossed arrows to either a two headed arrow pointing up and down, a two headed arrow pointing right and left, or a two headed arrow pointing at a diagonal.  Then you can hold your Left Mouse Button and drag the direction of the arrows to resize your Chart.

                With that said, how do you get your chart to show information?

                On the Design Tab there is the Data Group and in the Data Group there is a button called Select Data.  Click this Button and a Dialog Box will appear, it has the title Select Data Source.

  In the Chart Data Range it will automatically choose the Cell that your Cursor is in at the moment.  As long as you can see the black around white text all you have to do is select where you want the information to be pulled from.  Select Cells A5 to G16, you will see the Dancing Ants around the selected area.  You will also see =Charts!$A$5:$G$16 in the Chart data range text box.  Also there will also be information in the Legend Entries (series) section and the Horizontal (Category) Axis Label section.  Now Click OK.

                Is this more like what you expected?

                I tend to think that there is too much information in the Chart now.  Do you?

                That is okay because you can deal with that, as soon as I show you how that is.

                The first thing that you need to do is figure out what information it is that you want to see.  Do you want to see just the Totals or Just the Item information?

There are two ways that you can accomplish this task, I will show you how to do both.

             For now I will show you how to just show the Totals for the months.  The first thing that you need to do is go back to the Select Data Button, which is on the Design Tab in the Data group.

             There are a couple of ways that you can get the exact information that you want.  One way is slightly easier than the other.

                The first way that I will show you is working inside of the Select Data Source Dialog Box.

Months in the Legend

Product s in the Legend

                There is a button in the middle of the Dialog Box, it says Switch Row / Column, press this button and your information will change sides as seen in the sample above.  Once this is done, you can now remove the information that you no longer want or need.

                Click onto the White Bread so it has the Blue around it and press the Remove Button, continue to hit the Remove Button until the only items left are Total and Average, then remove Average.

                Now click the Switch Row / Column Button again and then click OK.  Now you should have a Column Chart that has the Totals in the main area and the Months in the Legend.  Notice that the Colors are corresponding, January is Purple, February is Dark Blue, etc, etc…

                Have you noticed that the data used to create the Chart has a Green Box around the Legend Information, a Purple Box around the Label “Total”, and a Blue Box Around the Data for the Totals.

                Now that you know how to get the information that you want the hard way I’ll walk you through getting the information an easier way, use the Undo Button, it is on the Quick Access Toolbar it looks like an Arrow that is Hooked to the Left, or you can use the CTRL + Z Buttons on the Keyboard.

                Now that you have the blank Chart once again, make sure that the Chart is still active; you will know that is active if you still have the Three Additional Tabs on the Ribbon.

                On the Design Tab click the Select Data Button and then use the Backspace Key, this should clear the Chart Data Range portion of the dialog box.  Hover over Cell A5 and hold your Left Mouse Button down and pull to Cell G5 then let go.  The Chart Data Range should now read =Charts!$A$5:$G$5.  Now hold your CTRL Key down and hover over Cell A15.  Once you are hovering over Cell A15 continue to hold the CTRL Key down and hold Your Left Mouse Button down and move to Cell G15.  The Cart Data Range should now read =Charts!$A$5:$G$5,Charts!$A$15:$G$15, click the OK button and you will see the Legend now says Total and you have the months Jan to June as the Labels for the Columns.

                Congratulations, you now know the Hard Way to create a Chart.

                There are some people who prefer to create the Charts this way.

                I am going to have you Delete the Chart now, click onto the Blue Border or the Chart and use the Delete Key.  By clicking the Blue Border you will ensure that the Chart has been selected instead of one of the objects inside of the Chart.

Chart Objects in Excel 2007

Chart Object, What they are

                The Objects are the Vertical Axis or Values, Chart Title, Series Legend Entry, Horizontal Axis or Category, Series Total, and many other portions (objects).

                Click on the Blue Surround that will make sure that the Chart is selected as opposed to one of the Objects within the Chart. 

                If you hover over the White Area, where the Blue Boxes are this is the Chart Area object.

                If you hover over the Grey Lines inside of the Red Box they are the Vertical Axis Major Gridlines, they are associated with the Numbers at the Left or the Values and the Columns. 

                If you hover over the White Area within the Red Box, this is called the Plot Area.

                The Object in the second sample in the Black Box is the Vertical Access (Value).

                Inside the Red Box is the Chart Title.

                The Object in the Green Box is the Legend.

                The Object in the Purple Box is the Horizontal Axis (Category).

            These Objects will change as to the information that you have in your Chart.  If you have the Product as opposed to the Months then the Objects will reflect the Product information.  There are also ways that you can manipulate the Objects, Change the Color, Change the Background Color or add a Picture.

                I will show you how to do all of this later on in the Charts Lesson in the book.