Creating Visual Breaks without using Cell Borders in MS Excel

Creating Visual Breaks without using Cell Borders in MS Excel

When setting up a worksheet where we need to separate one section of information from another, and present that separation clearly, we often use cell borders to accomplish the job.

Obviously that option is a great way to get the job done, but we tend to use those for everything… so making that visual break between data sections requires a bit of finesse with those borders.

What if we could create a visual break with a line created from a single character… let’s say an X or a ~ or maybe even a combination of X~? What about a word or phrase…

No, I’m not suggesting that you sit there and repeatedly hit a specific key or type a word over and over. If you’re going to use the line several times then you either do a lot of copy / paste work or end up with lines of different lengths… not necessarily the preferred situation.

Today I’m going to throw the use of the REPT formula out there for your consideration.

This formula allows you to state specific characters to be repeated and the number of times to repeat them without the hassle of actually entering each character separately.

Here’s how the formula is set up:

=REPT(“characters to repeat“, Number of times to repeat the characters)

For example, if I want a line of ~ marks that is 95 characters long, I would enter =REPT(“~”, 95) into the cell where the line should begin.

The result looks like this:

If you want a fancier line, try =REPT(“~**”, 60) to get this one:

Let’s look at the possibilities with actually marking it with text… such as confidential…

*** To get this line of characters into multiple cells at one time simply select all the cells where the REPT formula should be placed (use the Ctrl key to select non-adjacent cells), enter the formula then use Ctrl + Enter to put it into all selected cells at once.

Also, as I’m sure you noted when looking at the examples above, you can further change the appearance with the font type and other formatting tricks available to you; such as color, bold, size, etc…

Read More

Microsoft office excel 2007 - Features and Overview

Microsoft office excel 2007 - Features and Overview

Microsoft Excel 2007 has a brand new look and feel. The focus behind the change is an optimized, task oriented approach which translates to better spreadsheets prepared in less time. Let us take a look at microsoft office 2007 excel features like the following: Office Button Ribbon Quick Access Toolbar Shortcut Menu  Office Button : The Office Button in Microsoft Excel 2007 replaces the File menu available in previous versions of Excel. The new Office Button is shown below. The Office Button provides functionality common to all Office applications, including opening, saving, printing, and closing a file. Commands are listed on the left, and recently opened files appear on the right side. When I clicked on the Office Button in Microsoft Excel 2007, this is what I saw. As you click on the various commands, you will be given related options. For example, when I selected the Print command, I was given the flyout menu with additional print options like Print, Quick Print and Print Preview. Here is a screen shot shown below. Ribbon: In Microsoft Excel 2007, the old Menus and the Toolbars have been replaced by what's called the Ribbon. The Ribbon is what Microsoft is calling the new user interface. The idea is to give the user a task oriented interface where one will spend more time working on the actual workbook and less time looking for specific commands.  Included is a screen capture of what the Ribbon looks like. The Ribbon in Microsoft Excel 2007 is broken down into Tabs that deal specifically with a certain task. For example if you're trying to insert an object, you will find the related commands under the Insert Tab. The tabs are further broken down into logical groups. The Tabs (top) And that Groups (bottom) are shown in the figure below and highlighted using red rectangles. Quick Access Toolbar: The next new feature in Microsoft Excel 2007 that we are going to look at is the Quick Access Toolbar. The Quick Access Toolbar is a global toolbar which is present regardless of what tab you are on. Out of the box, Quick Access Toolbar has the Save, Undo, and Redo commands. Here is what the Quick Access Toolbar looks like on my screen. You can customize the Quick Access Toolbar to add the more often used commands. Let us say that you wanted to add the Spelling command to your Quick Access Toolbar. You can browse to the Review Tab and then the Proofing group. Next move the mouse over the Spelling command, right click and then select Add to Quick Access Toolbar. This will add the Spelling command  to the quick access toolbar and will be present at all times. These steps are shown in the next two figures. Shortcut Menu: The last thing we want to talk about is the Shortcut Menu sometimes also known as the Right Click Menu or Context Menu. The idea is to have common commands available to you as you're working on your spreadsheet. The Shortcut Menu changes depending on what screen and what application you are in. Here is a screen shot of what Shortcut Menu looks like on our Budget workbook.   If you notice the Shortcut Menu not only has formatting commands for cells, you can also view commands like copy, paste, change column and row settings and add comments. Also observe that on top of the shortcut menu, you will find the Mini Toolbar for an easy access and use  This concludes the lesson on Microsoft Office 2007 excel features and overview. If you are unable to find the information you are looking for, please visit Microsoft's Excel home page

Read More

Microsoft Excel 2007 - View Tab

Microsoft Excel 2007 - View Tab

In today's tutorial on Microsoft Excel 2007, we will look at the view tab. Using this Tab, you can control the layout and view of your Excel Workbook.  This is especially important when you are done working on a spreadsheet and are finally ready to print it.  So let us jump right into this and look at what the view tab in Microsoft Excel 2007 has to offer. The View Tab split in two five Groups: Workbook Views Group Show/Hide Group Zoom Group Window Group Macros Group Workbook Views Group:  Using the Workbook Views Group of commands, you can view your Excel Workbook in different layouts.  The Normal view is the default setting for Excel 2007.  This is shown in the figure below.  We are going to be using a Customer Workbook for today's practice.  Using the Normal view you are able to view the rows and columns as you work on your spreadsheet. The second view on the Workbook Views tab is Page Layout.  I find this particular view to be very helpful especially from a printing point of view.  Let us see what I am talking about.  For your current Excel Workbook on customer data, go ahead and click the Page Layout command in the Ribbon.  The effect of this action shown right below from our computer display.  Notice that now you are able to see the header block, all the margins around the worksheet, the vertical and horizontal rulers and the column and row headings appear differently.  If you were to print this work sheet, this is exactly what it would look like. Very nice! The next one is the Page Break Preview.  This is again beneficial if you are trying to print an Excel sheet that spans multiple pages.  This happens to be the case in our customer data worksheet.  When I clicked on this command, my monitor displayed the following screen picture.  Notice that I also got a dialog box letting me know that some information on page breaks.  If you look closely at the customer data, you will see that page 1 includes five columns, then a dotted line, and finally we can see page 4 including the Zip and Telephone columns. Custom Views the next option will let you use a personalized view of your spreadsheet.  You can even store this view so you can possibly use it on another workbook.  The last view is Full Screen which will let you maximize the Excel sheet on your monitor display.  Here's a screen shot of what I'm talking about.  Notice that you do not see elements like the Ribbon, Scroll Bars, and Quick Access Toolbar etc.  All you have is columns and rows of data so you can get more real estate on the computer. Show/Hide Group : The next set of commands falls under Show/Hide group.  These options are all listed as check boxes which can be turned on or off.  Let's play around with these commands next.  For this exercise I'm going to switch back to Page Layout view first.  This is what my data looks like before I do any make any changes First of all we are going to uncheck the Ruler option.  This will go ahead and remove the horizontal and vertical rulers as you can see the effect in the following screen capture. Next try unchecking the Formula Bar option.  Notice that it will remove the name box and the formula bar from your Excel Workbook as shown in the figure below. In the final option we will look at Headings check box which includes the row and column headings.  In the following computer display, you will notice that we do not have any column and row headings. Zoom Group: The next group we will look at is the Zoom Group.  Using these commands you can control the area of your workbook that can be displayed on the computer monitor. The default is 100% which is what we have in the following screen capture.  Also observe that we have switched back to the Normal view from the Page Layout for these steps. When you click on the Zoom command, you will get a new dialog box titled Zoom.  Here you can direct the magnification level.  It has a few preset options in addition to a custom choice where you can enter your own magnification level.  For now go ahead and select 50% then click OK.  After you click OK notice the effect of this action in the screen capture below.  Now you can see all the fields related to your customer information.  The size of the Headings has been adjusted to fit the Zooming level as well.  If you look on the bottom right corner of the screen shot, you can also see the Zooming toolbar with the same value of 50%.  This is another place where you can control the same functionality. Let us go ahead and click on 100% which is the next command in the Zoom Group.  This will convert the spreadsheet back to its normal magnification level. The last option is by far my favorite in this group.  What if you wanted to highlight certain section of your Excel Workbook? No problem.  Let us say that you wanted to see only 10 customers and the first four columns.  You can select all the cells from A1 through D11 and then click on Zoom to Selection on the Ribbon. The end result of this action shown in the screen display. Window Group : Sometimes it is necessary to work on the same Excel sheet, however using multiple windows.  The Window Group under the View Tab in Microsoft Excel will let you do just that.  Let us take a look at these options next.  Switch back to Normal view at 100% level and then click on New Window command on the Ribbon.  This will open up a new Excel Workbook and title it Customer Data.xlsx:2.  Shown right below in the screen capture of this step. Now that you have multiple copies of the same document, you are able to see them as the same time.  Arrange All command can help you with this task.  What you click on it, you will get the following dialog box as shown below.  This is where you can choose how you would like to see the Windows.  For now go ahead and choose Vertical and then click OK. The outcome of this action shown in the following screen capture.  You now have the same Excel Workbook shown in a vertical fashion.  This arrangement will let you view different sections of the same data at the same time, Sweet!  Just realize that when you make changes in one, it will affect the other Excel Worksheet also. Moving onto the next command Freezing Panes which I think is a lifesaver that you are working with a complex spreadsheet.  Using this great functionality, you can freeze particular rows and columns even as you walk around in your worksheet.  Let us see in action next. Our customer data sheet only has row headings.  As such we do not need to worry about freezing columns.  We would like to keep the Headings as we scrolled through my data.  How can we do that? You can click on Freeze Panes and then choose Freeze Top Row from the drop down list. We have included a screen shot of this step. Now as you move around in the Excel sheet, sideways or bottom, the column headings will stay frozen in place.  For example in the following figure, we are looking at customer 46 yet we are still able to see the first row which includes column headings.  This is Awesome! Let us take a look at a few more options under the Window Group.  Using the Split command, you can essentially break your spreadsheet into different parts.  This again can be useful when you need to look at different areas at the same time.  You can select the cell where you would like the split to happen, for example we have chosen B32 as my Split Point.  Notice our workbook now is split into four separate areas which we can view concurrently. When working with multiple Windows, the View Side by Side command will let you look at the data in a horizontal fashion.  By default it also enables the Synchronous Scrolling feature, as you move up and down, the data in both windows will move in sync.  You can disable this feature if you do not want synchronous scrolling feature. The final option we will look at is how to switch windows under the Window command.  Once again if you have multiple copies of the same worksheet, you can use this command to toggle between windows back and forth.  We have included a screen capture on our customer data spreadsheet.  Notice that now we have a drop down which will let us switch to one of our open windows.    This concludes the microsoft tutorial on excel 2007 view tab.

Read More

Microsoft Excel 2007 - Review Tab

Microsoft Excel 2007 - Review Tab

In today's Microsoft Tutorial on Excel, we will cover Review Tab.  This Tab has functionality that will let you proof read your Excel workbooks, add and delete comments, protect and unprotect Excel sheets/workbooks and finally allow users to track changes in a multi user Excel workbook. The review Tab has the following Groups: Proofing Group Comments Group Changes Group  For our practice today we are going to revisit the Grades Excel workbook that you have already seen in a prior lesson.  Here is a screen shot of the Review Tab. Proofing Group : The first Group that we will look at is Proofing.  This has commands for checking spelling and grammar, using research and Thesaurus and ability to translate from one language to another.  Let us take a look at some of these commands next.  You are done working on your grade book and would like to check the spellings.  You can click on spelling command which will invoke the spelling dialog box as shown below.  Notice that it found an incorrect spelling in cell L2. It also made some suggestions for the correct word which is Percent.  Go ahead and click on Change to accept the suggestion.  Remember you can also ignore ones that are correct or do not need to be changed. The Spell Checker will skip over such words and continue to check rest of your Excel sheet. Next we will look at the Research command which can be beneficial to look up information using online reference sources.  For example I selected the word average in cell A15 and then clicked on Research on the Proofing Group on the Review Tab.  This invoked the research dialog box on the right side as visible below.  As you scroll down a little bit, you can see that it found not only the correct pronunciation but some common meanings for this work, very cool! The next feature is the one that I use a lot as I find myself repeating the same words over and over again.  Using Thesaurus, Microsoft Excel 2007 will suggest words with similar meanings which can be used as alternates.  On the computer screen capture below, you can see that it found the word Mean as an alternate to Average. The Translate command can be quite handy if you happen to work in a multilingual environment.  Let us say that you would like to change the word average from English to Arabic. You can click on translate command which will bring up a new dialog box which I have expanded here so you can see the options a little bit better.  Notice that not only did it suggests the word but was able to show the word in Arabic as well. Comments Group: The next set of commands has to do with the Comments Group in Microsoft Excel 2007.  if you happen to be working on a complex Excel project with several other people, it may be hard to track all the comments and changes from different sources.  Using the comments functionality, Excel will let you add your notes and even include the user information. How about we try to add a few comments?  For the student Joe Reboot, I feel the project points are way too low.  So I went ahead and clicked on New Comment under Comments Group. This added a yellow comment box with my name and a blinking cursor around it. This is shown right below.  A red triangle in the upper-right corner of the commented cell is also visible for easy location Similarly I added another comment in cell E12 at for Jason’s midterm score.  I went ahead and saved the document.  Let’s assume that someone else opens this Excel sheet and would like to review all the comments so for.  They can easily click on the command Show All Comments.  Excel displays all comment boxes on the current worksheet. Clicking the Show All Comments button again turns off the comment display.If the user has proper access, they can delete these comments and create their new ones. Changes Group: Microsoft Excel 2007 has quite a bit of functionality in the security arena. This is a big improvement over the prior versions like Microsoft Excel 2003. There may be times when you would like to keep confidential information secure from modification and even control the structure of the workbook in Excel 2007. Protect Sheet command will prevent users from accidental updating or deleting vital information from the spreadsheet.  You can click on Protect Sheet under the Changes Group.  This is going to bring up the time a dialog box titled the Protect Sheet.  This is where you can enter a password along with several other options to limit actions in your worksheet.  Go ahead enter a password and leave the default settings and then click OK.  This is shown in the following figure. Next Excel 2007 will you will ask you to reenter the password to enable the security feature.  Here’s the Confirm Password dialog box that I got on my computer screen. You can now close the worksheet. After protecting the sheet we need to test if this actually work. Open the Grades Workbook again and try to change data in Excel sheet. Notice you were unable to do so and you also received a dialog box similar to the one below informing you that this data is read only.  If you wanted to make modifications, you would need to remove the protection using the Unprotect command under the Changes Group. Go ahead and click on Unprotect sheet command.  This will invoke the dialog box where you are prompted to enter the password.  Enter the password and then click OK.  Now you are back to your original sheet where you can make modifications to the data.  Awesome! In addition to protecting sheet you can also protect your workbook in Microsoft Excel.  This prevents changes to the structure of the workbook and can also be utilized to control window functions like minimizing or closing worksheets.  How about we try this functionality next? When you click on protect workbook command, you will get a dropdown where you will select Protect Structure and Windows. Next You will get a similar dialog box to the one we have seen before. Go ahead and check the options for structure and windows, entered a password and then click OK.  The changes from the previous step are quite subtle but you can see them if you look closely. The first thing that you will notice is missing window resizing buttons in the top right area and highlighted by the red rectangle.  The second thing you’ll see is on the right click menu and, the commands for altering the worksheet properties are now disabled.  We have included a screen shot of this right below. The last functionality that we will look at is protecting and sharing your workbook.  This will let you protect your data using a password when working on a collaboration.  In addition you can enable tracking changes using this command as.  When you click on Protect and Share Workbook, you will get the following dialog box.  This is where you can check the box for sharing the track changes by entering a password. Microsoft Excel 2007 invokes a new dialog box titled Highlight Changes.  Here you can choose which options you would like to  enable for tracking changes.  I checked the boxes for When, Who and Where.  You need to make sure that in the where clause text box, you highlight all of your tracked data. Finally  click OK to complete the steps. TextNext proceed to make a few changes in your workbook. In our case, we made a comment about the midterm average being too low in cell E16.  We also changed to score for Paulee Manson for chapter 4 and finally added another comment in the first row under column J.  Go ahead and saved the document.  Now if your colleague would like to know what changes were made?  They can simply opens this Excel Workbook, and then highlight changes using Track Changes command from the Changes Group.  They will be prompted with the same when, who, where dialog box.  They would need to check all the necessary boxes and then click OK.  Next they will be able to see all the changes that need to be either accepted or rejected highlighted by a blue rectangle.  Here is a screen shot of what it looks like shown below. This concludes the Tutorial on Microsoft Excel Review Tab.

Read More