"Excel"ing to Create Subtotal Row Totals

Your manager sends you an e-mail with a spreadsheet attached. They ask you to create subtotals for the latest sales report, but want you to only send them the revised spreadsheet showing the subtotal rows.

I recently ran into this problem as well. One of my managers wanted me to use our sales tracking software to create the sales report for a particular product. Then he mentioned that he didn’t want all of the details of each product listed. He only wanted the total amounts shown as a bundled figure.

This made my life a little difficult since I thought that I would have to copy and paste all of the subtotal amounts into this spreadsheet, as the report shows all of the details. On a side note, if you need help creating subtotals, check my post on using subtotal functions in Microsoft Excel.

I poked around the internet a bit and came to realize that there is a faster way of doing this. I was able to put the entire report together within a matter of minutes.

Once you’ve created your subtotals, follow these steps on creating only subtotal rows:

1. Highlight everything from the headings to the grand total.
   
   
   


2. Click the little number 2 shown below to collapse all of the details in between all of the companies.
   
   


3. Click Edit, Go To.
   
   


4. Click Special.
   
   
   
   
5. Click Visible cells only and click OK.
   
   
   


6. Click Edit, Copy or CTRL+C to copy.
   
7. Open a new workbook and then click Edit, Paste or CTRL+V.
   
   
   

Now only the subtotal rows will show up, instead of all of the details in between.

Subscribe to The Professional Assistant feeds or get posts e-mailed directly to you today.

Until next time,

Take care - of your clutter!


Post from: Administrative Assistant

Continue reading...

"Excel"ing to Convert Text to Numbers

Have you ever had an issue where you copied and pasted figures from a spreadsheet that was text based (not Microsoft Excel format) and noticed that you can't get a total using the sum function?

This recently happened to me, as one of the managers that I work for asked me to create a sales tracking spreadsheet for one of our products. The issue is that we use one piece of software to track all of our sales, but the output is always in Microsoft Excel format.

When I export data into Excel, it shows up as text, as opposed to numbers. The only problem is that I have a large amount of figures that I would have to manually re-enter, which becomes cumbersome and very time consuming. The good thing is that all of the data is in one column, but you can use this method for as many columns as you like, it's just that you would have to repeat it for each column one after another.

There is a simple resolution to this. Here are the 7 simple steps on doing this:

1. Click on the letter of the column that you need to convert, to select the entire column.
   
   
   
   
   
2. Click Data, then Text to Columns.
   
   
   
   
   
3. Under Original data type, click Delimited, then click Next.
   
   
   
   
   
4. Under Delimiters, check the Tab box, then click Next.
   
   

   

5. Under Column data format, click General.
   
   

   

6. Click Finish.

Now you can create reports that you can add up or even use any other function that you would like.

Subscribe to The Professional Assistant feeds or get posts e-mailed directly to you today.

Until next time,

Take care - of your clutter!


Post from: Administrative Assistant

Continue reading...

Importing Web Pages into Microsoft Excel 101

Your manager comes up to you and asks you to open a web page. You see that there is quite a large amount of data on this particular page, looking like a spreadsheet. The manager then asks you to add this information onto an existing Microsoft Excel spreadsheet and for you to manipulate the data so that it flows with the original file.

 

Oh, one more thing, the manager asks you to do this right away, as they need it for a meeting in one hour.

 

Ok, don’t panic. There is a very simple method of adding data directly from the web page onto a Microsoft Excel spreadsheet.

 

1. Click File, Open. 

2. Type in the web address of where the data is located, including the http://. 

3. Make sure to use Web Pages and Web Archives (*.htm; *.html; *mht; *mhtml) in the Files of Type section below where you’ve entered the web address. 

4. Click Open. 

As you can see, the entire web page imports directly into Microsoft Excel. You can now remove any useless information and have all of the data copied and pasted directly into your original spreadsheet.

Subscribe to The Professional Assistant feeds or get posts e-mailed directly to you today.

Until next time,

Take care - of your clutter!


Post from: Assistant Tips

Continue reading...

Filtering The Right Results in Excel

You have a tremendously large spreadsheet in front of you. Your boss asks you to find client information on particular criteria. You look at your spreadsheet and think to yourself, "This will take me forever. I would have to find every client that has these criteria."

 

Thankfully, Microsoft Excel comes equipped with a very easy to use feature called "Auto Filter". This function allows you to be able to filter as many criteria as you wish from an existing spreadsheet. It really cuts down on the amount of work you have to do in finding this information.

 

Here are the 4 simple steps on using this function:

 

1. Open the file that you want to gather information for. 

2. Click on the cell shown below. Then the entire spreadsheet will be highlighted.

3. Click on Data, Filter, AutoFilter.

 

4. Now you will notice that there are little boxes for each column with drop down arrows. You can now select the particular criteria in each column that fits your query. You can use as many drop down arrows or filters as you like to narrow down your search. 

Tip: If you are looking for particular words within the filters, you can click (Custom…) and type in the information you are looking for.  Just make sure to use the "and" and "or" commands correctly.

Subscribe to The Professional Assistant feeds or get posts e-mailed directly to you today.

Until next time,

Take care - of your clutter!

Post from: Assistant Tips

Continue reading...

"Excel"ing With Links

As you may have noticed, I am in the process of writing a series of Microsoft Excel articles. Many people at work, including my wife, have been bombarding me with questions about it and thought that I’d try to recall these questions and try to answer them to the best of my ability. I will also be writing about other Microsoft Office products in this series.

 

Have you ever wondered how you can link information between files in Microsoft Excel? For example, you want to show last year’s sales figures on this year’s sales figures spreadsheet, but your boss wants you to have two separate files for each.

 

Here are the 4 simple steps on how to do this:

 

1. Open or create the new spreadsheet with the destination cell (such as this year’s sales figures). 

2. Click on the particular cell that you want the information to go to, then type “+” without the quotes, on either the new spreadsheet or on the particular sheet that you want to copy the information to (sheets are tabs at the bottom of your Excel spreadsheet). 

3. Click on the cell that has the source information (such as last year’s sales figure) and hit enter. 

4. Make sure to save the new or opened file. Now when you open it, make sure to click “Update” as this will update any changes to the original file (where you had your last year’s sales figures). 

You can do this for as many cells and as many files as you like. Just make sure not to delete the originating file, since the link will be broken at that point, since Microsoft Excel can’t read the source data.

Subscribe to The Professional Assistant feeds or get posts e-mailed directly to you today.

Until next time,

Take care - of your clutter!

Post from: Assistant Tips

Continue reading...

“Count”ing In Microsoft Excel

You are near completion of a sales tracking spreadsheet and your boss asks you to find out how many clients have purchased a particular product, how many times they have purchased this year, last year, etc. You think to yourself, “How would I get this done, since it’s such a large spreadsheet and would take me hours to count it by hand?”

 

There’s a very simple solution to this problem. There are two functions in Microsoft Excel that can help you accomplish this task within a matter of seconds.

The two functions are “count” and “counta”. Don’t worry, the latter one isn’t misspelled.

 

1. The “count” function is used for counting numbers.
2. The “counta” function is used for counting everything but numbers, such as client’s names, company names, etc. 

To use the count function:

 

1. Click on the particular cell that you want to have your count shown in.
2. Type “=count(“ without the quotes, then select the range of cells that you want to capture, such as a list of sales, then type “)” without the quotes and hit enter. 

To use the counta function, simply follow the steps above, but replace “count” with “counta”. Make sure that you are using this function for non-numerical data.

 

Now you can show your boss that you can get these details in a snap!

Subscribe to The Professional Assistant feeds or get posts e-mailed directly to you today.

Until next time,

Take care - of your clutter!

Post from: Assistant Tips

Continue reading...

View Microsoft Office Files Without The Software

You're sick at home, can't seem to get access to your computer at work through your computer at home and your boss calls you up and asks if you can review a particular file. You scratch your head and start wondering, "How do I go about doing this?"

 

I'm sure that almost every company has computers and Microsoft Office software installed for all of their staff members. But there are times when you need to access certain Microsoft Office files that you cannot open, due to the fact that you don't have the software installed, for example, on your home computer.

 

You can download the following viewers for Microsoft Office files, where you don't have to purchase the entire piece of software. This enables you to review files, just like Adobe PDF Reader does.

 

• Microsoft Excel Viewer 

• Microsoft Word Viewer 

• Microsoft PowerPoint Viewer 

Now you can rest assured that your boss will get the information they need from you.

Subscribe to The Professional Assistant feeds or get posts e-mailed directly to you today.

Until next time,

Take care - of your clutter!
 

Post from: Administrative Assistant

Continue reading...

Microsoft Office Poll Results

I am sure that all of you use Microsoft Office in your workplace. There really isn't a chance that you don't use it, whether you are on a PC or a Mac.

 

The results actually surprised me quite a bit, since some of you consider yourselves gurus. If you have any suggestions on how to make things run more smoothly in Microsoft Office, please let the rest of us know.

 

I was not surprised that anyone would be a novice, since in order for you to get a job these days anywhere in an office setting, you should at least have an intermediate understanding of these applications. Otherwise, you would be working somewhere else.

 

If you have any tips and tricks that you would like to offer, please click the comment link below (if you are reading this via feed or e-mail, click on the title of this post first) and inspire us.

If you like this post, please subscribe to my feeds or get posts e-mailed directly to you.

Until next time,

Take care - of your clutter!


Post from: Administrative Assistant

Continue reading...

Work From Home? Want to be More Productive?

Are you a Virtual/Professional Assistant or want to become one? Do you work from home on a regular basis? Are you just starting out or already have a thriving business?

 

Finding the right tools to manage your business can be time consuming. You have to understand how a professional administrator operates and also have the technology and software to run your business in a successful manner.

 

Virtual Assistant Manager is a website that I recently stumbled upon which helps you and/or your organization thrive in this type of environment.

 

Here are some core benefits that were taken directly from the site:

 

• Allowing clients to enter in their own task requests. This lessens the time you have to spend on the phone. 

• Checking task statuses, task assignments, due dates, and task notes at a glance.  No need to access multiple tools to access your information. 

• Having all your communication with your clients be in one place, so there is never a question of when or if an e-mail was sent. All communication history is saved. 

• Easily recording the time spent on each task. You can then print a summary report to Excel of all of your hours for any time period, for any task, or for any client. 

To find out more of how Virtual Assistant Manager works, click here.

 

Now you can show your boss that you are finding ways to be more productive and a team player. You never know, you might get a raise after letting them know about this excellent opportunity.

If you like this post, please subscribe to my feeds or get posts e-mailed directly to you.

Until next time,

Take care - of your clutter!


Post from: Administrative Assistant

Continue reading...

Using Subtotals Function in Microsoft Excel

Do you have a Microsoft Excel spreadsheet where you have firm names and figures? The spreadsheet might contain other types of data, but is similar to this example?

Imagine your boss comes to your desk and asks you to provide him/her with details of how much each firm earned in revenue on this spreadsheet. You think to yourself, how can I do this quickly, without much manual work?

Here are the 6 simple steps to using the subtotal function in Microsoft Excel:

1. Highlight the firm names and figures.

2. Click Data, then Sort, then sort by the firm name.

3. Highlight the firm names and figures again.

4. Click Data, then Subtotals, click OK.

5. Select "At each change in:" should be Firm1, "Use function:" should be Sum and "Add subtotal to:" should only have $100 .00 checked (in this example). You only need to have "Summary below data" checked if you don't currently have subtotals shown.

6. Now you have the total for each firm as well as the grand total listed.

If you need to do this for other functions, make sure to change the "Use function:" section to whatever type of calculation you need to do for your particular project.

If you like this post, please subscribe to my feeds or get posts e-mailed directly to you.

Until next time,

Take care - of your clutter!

Continue reading...

Copying Tables, Pictures or Text in PDF Files to Other Sources

Did your boss just ask you to copy a table, picture or text to another source of software, such as Microsoft Excel or Word? Are you struggling with trying to figure out how to do this in time?

This is actually quite a simple task to accomplish. There are two different ways to work within PDF files:

1. To copy a picture, simply click on the following icon shown below and the software will automatically copy it onto the clipboard:

2. All you need to do now is to paste it into the piece of software by either right-clicking your mouse and select paste or high CTRL-V.
   
   
   

1. If you want to copy text from a PDF file into another source, click on the following icon shown below. Then make sure to either right-click your mouse, then select copy or hit CTRL-C.

2. Paste the text into the software by either right-clicking your mouse and select paste or high CTRL-V.

Just don't forget to save your files. You'd be surprised as to how many people forget to do this.

If you like this post, please subscribe to my feeds or get posts e-mailed directly to you.

Until next time,

Take care - of your clutter!

Continue reading...

Opening Microsoft Office 2007 Files in Microsoft Office XP

I recently had a vendor send us an Excel spreadsheet that he created in Microsoft Word 2007 format. Unfortunately, our firm still only has Microsoft Office XP and was curious as to how this would work.

I remember in the past that you can always downgrade files. For example, if I have Microsoft Excel XP and would like to send a file to a client that only has Microsoft Excel 97 (which was a horrible disaster for Microsoft, since there were quite a few bugs in this version), you could save the file as a Microsoft Excel 97 version. The only downfall was that if you accidentally sent the client a Microsoft Excel XP version, they wouldn't be able to open it.

The good news is that Microsoft has come out with a patch for this. If you receive a file that was written in the Microsoft Office 2007 format, you can download the patch called Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats.

You can now go ahead and open any file in this format. It will take a short period of time to convert the file each time you open it, but it saves you time and money in the long run.

If you like this post, please subscribe to my feeds or get posts e-mailed directly to you.

Until next time,

Take care - of your clutter!

Continue reading...

Online Operating Systems?

Are you having trouble trying to connect to your office's or home's computer? Do you want reliable speed and control (provided that you are using a faster Internet connection)?

Here's a new solution that a few firms have come up with - Online Operating Systems!

All of your information is kept online, just like Google Docs, except you can actually either replicate your own desktop or create a new one with all of the same software and features. You can receive 1GB of space for free from most providers. If you want more, it will cost you and that depends on which service provider you choose.

Here are some of the links to these providers:

• www.goowy.com
• www.desktoptwo.com
• www.eyeos.org
• www.glidedigital.com
• www.youos.com

If you decide to use any of these services, let me know how it works out for you (advantages and/or disadvantages).

If you have any questions, suggestions or comments, please feel free to click on the "comments" link below and leave some feedback. If you are reading this via feed or e-mail, please click on the title link and post your comment via web.

Until next time,

Take care - of your clutter!

Continue reading...

Networking Office Documents

Do you have trouble getting onto your company's network while out of the office? Do you need to access that spreadsheet or presentation right away? Here's a quick solution for you - Google Docs.

If you need to change or create any new Word, Excel or Powerpoint document, this is the place to do it - anywhere you are, as long as you have an internet connection. You can share the files with anyone that you want in a very secure manner.

If your boss is asking you to change some information on a spreadsheet, you can simply log into the website, make the changes and your boss would be able to see them (in real-time). Just make sure that you provide access to the person that needs to see these documents. You can open a chat window to discuss with your boss of the changes that you've made or are going to make. You can even present information from these documents to everyone that's signed on at that time.

Click here to see a video of how Google Docs works. The video is not of high standards, but it explains the simplicity of its functions.

If you have any questions, suggestions or comments, please feel free to click on the "comments" link below and leave some feedback.

Until next time,

Take care - of your clutter!

Continue reading...

How to Copy Tables from Word to Powerpoint

People often ask me, "How do you copy a table from Word into Powerpoint?"

This actually is quite easy to do. Here are the simple steps on how to copy tables:

• Open your Word document that contains the table.
• Open Powerpoint and click on File, then select New, then Blank Presentation.
• You will see this screen at the bottom. Click on the picture in the right-hand side frame (make sure to scroll all the way down):

• Select your table by going to the top left corner of it and you will see a little + type sign, except it will show arrows pointing in 4 directions (in Word).
• Click copy.
• Click the middle section of the page (single click) and click on paste.
• You might need to do some formatting on Powerpoint, since it only allows for so many rows.

Now you can show your boss the new presentation that they were asking for, without getting your stress level up.

If you have any questions, suggestions or comments, please feel free to click on the "comments" link below and leave some feedback.

Until next time,

Take care - of your clutter!

Continue reading...

How to Create Mail Merge Letters

Ever have trouble doing a mail merge in Microsoft Word? The mail merge wizard is the simplest tool to use to get your letters or e-mails out to your clients.

Here are the basic steps to take to do a simple mail merge (make sure you have either a contact list created in Excel or Access for this, otherwise you can create a list within Word, once asked):

1. Start Word.
2. Click on Tools, Letters and Mailings, and finally Mail Merge Wizard.
3. Select if you want to merge contacts into a letter, envelopes, labels or a directory.
4. Next, select if you want to use your current letter that you have loaded, use a template that you may have or start from another letter that you created.
5. Next, you have to choose if you want to select Outlook contacts or contacts from an Excel or Access list. If you are choosing contacts from Excel, select the file and the particular sheet that they are on. If it’s an Access file, select the query or table that your contacts are in. You can also create a brand new list of contacts if you wish.
6. Click all of the boxes with contacts that you want to include.
7. Click on “More Items” on the right hand side and click the particular fields that you want to merge on the particular parts of the letter that will change with each contact.
8. Next, you can preview all of your letters, if you wish, but I usually go to the next step, since you will be able to see all of your letters one after the other.
9. Here, click on “Edit individual letters”, then click “All” and a new window will pop up.
10. You are now ready to review and print all of your mail merged letters.

And don’t forget to save your work!

If you have any questions, suggestions or comments, please feel free to click on the "comments" link below and leave some feedback.

Until next time,

Take care – of your clutter!

Continue reading...

Calculation of Discounts in Excel

Have you ever wondered how to calculate discounts on certain products/services that you or your firm offers? One of my colleagues had this dilemma a few days ago.

I tried searching around for an answer on Google, but no luck. It kept providing me topics that weren't relevant to the particular answer I was looking for. I then checked Excel's help file and there it was.

Here is how to calculate the difference between two numbers as a percentage:

For example, your earnings are $2,342 in November and $2,500 in December. What is the percentage change in your earnings between these two months? To do this task, use the ABS function and the subtraction (-) and division (/) operators.

A = November earnings: 2342
B = December earnings: 2500

The formula for this is:

=(B2-A2)/ABS(A2)

Description:

Divides the difference between the second and first numbers by the absolute value of the first number to get the percentage change (0.06746 or 6.75%)

And don't forget to click the % icon to format it into a percentage.

If you have any questions, suggestions or comments, please feel free to click on the "comments" link below and leave some feedback.

Until next time,

Take care - of your clutter!

Continue reading...