Kategorienarchive: Automation Guru

Convert PDF table data into editable Excel files for free
Zum Original Blog Automation Guru

Convert PDF to Excel for Free

PDF is one of the most used file formats today, especially in the business world. There are many advantages and benefits that come with using PDFs. Firstly, PDFs are compatible and can be opened on every platform. PDFs are also compact, which makes them very easy and quick to transfer and send.

One of the disadvantages of PDFs is that they are not easily editable. Thankfully, there are tools that can take PDFs and convert the information contained in the PDFs into a much more editable file format.
In the business world, a lot of information is presented in charts and tables, and many times, this information is shared through PDFs. However, editing this data in PDF is often impossible.

Compare values – Chapter 5
Zum Original Blog Automation Guru

It’s common to compare values. For instance, you might track inventory levels by comparing the stock on hand to a reorder level. Using conditional formatting, you can alert users when it’s time to reorder, as shown in Figure J. Select the values you want to format — in this case, that’s B2:B46. (You could highlight the entire row or one of the inventory values.) Then, apply the format shown in Figure K.

Figure J

Highlighted items that are running low.

Figure K

Create a dynamic record highlight – Chapter 4
Zum Original Blog Automation Guru

Highlighting an entire record (Chapter 3) is convenient, but you might want the conditional format to be more… conditional. For instance, suppose you want users to choose the category on the fly, as shown in Figure G. First, use the Advanced Filter feature to copy a unique list to an out-of-the-way spot, as shown in Figure H. Then, use the Data Validation feature to create a list, also shown in Figure H.

Figure G

Let users choose the highlighted category from a validation list.

Figure H

Highlight a row based on a single value – Chapter 3
Zum Original Blog Automation Guru

Filters are great for limiting what you see, but sometimes you want to compare records. When this is the case, conditional formats can distinguish records. Figure E shows a data set of products with a conditional format highlighting only Condiment records.
 

Figure E

 
 
 
You can distinguish records based on a single value.
 
Select the entire data range (not the column headings) so Excel can format the entire record (row). Figure F shows the formula-based settings. The $G2 component creates a relative address, which updates with each row: G4, G5, G6, and so on. When the value in the referenced cell equals the string “Condiment,” Excel highlights the entire row.
 

Conditional Formatting – Chapter 2
Zum Original Blog Automation Guru

Display simple icons

 
Using conditional formatting (in 2007 and 2010), you can display icons that are often easier to interpret than the values they represent. For instance, a simple checkmark might be quicker to discern than the text value yes, on, true, and so on. Figure C shows an icon solution for the same rule violation in Chapter 1.
 

Figure C

Use icons instead of traditional formats to represent conditional rules.

First, select the values in column E and apply one of the default icon sets. Then, use Manage Rules to manipulate the results. Figure D shows the final settings (click Reverse Icon Order first).
 

Figure D

Conditional Formatting – Chapter 1
Zum Original Blog Automation Guru

Distinguish business rule violations

Using conditional formatting, you can visually discern when something is breaking a business rule. For example, Figure A shows a simple timekeeping sheet that highlights a workday that’s greater than eight hours. Why? Because your organization requires approval for anything over an eight-hour day.

Alert users to overtime hours requiring approval.

Formula for Elapsed Time: =($C2-$B2 + ($B2>$C2))-$D2

To set the Conditional Formatting in Excel, do the following:

• With your Elapsed Time Range, click on the Home menu at the top of Excel
• Locate the Styles panel, and the Conditional Formatting item:
• Select New Rule

Working with the time values complicates things a bit, as you can see in Figure B. This solution uses >.34 to represent time values greater than eight hours, which will work in most cases — you can’t use the value 8 or even the time value 8:00. Or you could use the predefined Greater Than rule in Excel 2007 and 2010, which will automatically use the more accurate value of 0.333333….(8/24)

Brackets in formula
Zum Original Blog Automation Guru

Sometimes you will need to use brackets, (also known as ‘braces’), in formula.

This is to ensure that the calculations are performed in the order that you need.

The need for brackets occurs when you mix plus or minus with divide or multiply.

Mathematically speaking the * and / are more important than + and – .

The * and / operations will be calculated before + and - .

Example 1 : The wrong answer !

You may expect that 10 + 20 would equal 30 and then 30 * 2 would equal 60

But because the * is calculated first Excel sees the calculation as 20 * 2 resulting

in 40 and then 10 + 40 resulting in 50.

DYNAMIC FORECASTING TOOL
Zum Original Blog Automation Guru

If you’ve ever wanted to forecast the remainder of a month’s sales, based on the last few weeks, this is the cure for you. This tool allows you to forecast the rest of the month by averaging the number of Mondays, Tuesdays, etc. remaining in the month. You can even exclude previous high or low days from the forecast. Easily expandable to multiple items in one clean, macro free, sheet. Enjoy!

http://excel-doctors.com/tools.html

http://excel-doctors.com/includes/Dynamic%20Forecasting%20Tool%20-%20excel-doctors.com.xlsx

Zum Original Blog
Autor: Automation Guru

DYNAMIC FORECASTING TOOL
Zum Original Blog Automation Guru

If you’ve ever wanted to forecast the remainder of a month’s sales, based on the last few weeks, this is the cure for you. This tool allows you to forecast the rest of the month by averaging the number of Mondays, Tuesdays, etc. remaining in the month. You can even exclude previous high or low days from the forecast. Easily expandable to multiple items in one clean, macro free, sheet. Enjoy!

http://excel-doctors.com/tools.html

http://excel-doctors.com/includes/Dynamic%20Forecasting%20Tool%20-%20excel-doctors.com.xlsx

Zum Original Blog
Autor: Automation Guru

THE STORY OF A LOOKUP
Zum Original Blog Automation Guru

Hello again,

Finding things and putting them where you want them. Vlookup, hlookup, match.

http://excel-doctors.com/video_2.html

http://www.youtube.com/watch?v=TYDWn3yGBeM

I hope you enjoy the content.

Regards,

Jorge

Zum Original Blog
Autor: Jorge

THE EXCEL KEYBOARD
Zum Original Blog Automation Guru

Hello again,

This video shows the most common keys on your PC keyboard. It also covers navigating within a sheet.

http://excel-doctors.com/video_1.html

http://www.youtube.com/watch?v=5xaCP__nW8o

I hope you enjoy the content.

Regards,

Jorge

Zum Original Blog
Autor: Jorge

Selecting Data & Basic Filtering
Zum Original Blog Automation Guru

Three basic techniques for selecting a data set, how to sort a data set, how to apply filters and the pivot table short cut.

http://excel-doctors.com/video_4.html

I hope you enjoyed the content.

Regards, Jorge

Zum Original Blog
Autor: Jorge

Pasting Specially
Zum Original Blog Automation Guru

Learn how to paste special values, formats and formulas. Also covered are the properties of cells. Transpose, Paste Multiply. Basic Copy & Paste.

http://excel-doctors.com/video.html

I hope you enjoyed the content. Regards, Jorge

Zum Original Blog
Autor: Jorge

Creating an Assessment Tool in Excel
Zum Original Blog Automation Guru

This Article shows users how to create an assessment tool for their classroom/office. This testing system is automatically graded and scored, which aids the instructor in the testing process. In this tutorial, security measures are built into Excel workbook, which ensure testing integrity. This tutorial can be modified after learning the appropriate steps to any mode of testing.

The First Steps

1.  Open Microsoft Excel to a blank workbook.

2.  Save the workbook to with a filename that is appropriate for your needs by clicking File>Save 

      located on the standard menu.                                                                                      

Participate in AutomationGuru and WIN an Apple 2GB iPod shuffle
Zum Original Blog Automation Guru

I am very excited to announce our First Contest! We want to say thank you to all of our loyal readers as well as welcoming new visitors by offering a chance to win an Apple 2GB iPod shuffle.

How to WIN it?

I know, you don’t want to miss your chance to win.  There are 2 easy ways to enter into the contest. So here’s how to get in:

Participation in Forum – Solve more and more Excel/Access Topics in AutomationGuru Forum.

Participate in Articles – Post the Articles on Excel/Access or any other MS-Office applications.

Additional Important Contest Information

1. Whoever successfully solves the most number of Topics and posts the most number of Articles wins an Apple 2GB iPod shuffle.

Filename Formula
Zum Original Blog Automation Guru

Hi Friends,

There may be times when you need to insert the name of the current workbook/worksheet/path in to a cell. Below are the formula’s to do these taks -

To exercise the below formula’s, Please assume –

Your Workbook path = D:\Prabhat\Macro\

Workbook name = Excel Help.xls

Worksheet name = Filename Formula

Note: Please copy the formula’s and remove the leading blank space to do the excercise. Thank you. 

Regards,

Prabhat

Zum Original Blog
Autor: Automation Guru

Split First name, Surname and Middle name
Zum Original Blog Automation Guru

The following formulas are useful when you have one cell containing text which needs to be split up.

One of the most common examples of this is when a person’s Forename and Surname are entered in full into a cell.

The formula uses various text functions to accomplish the task. Each of the techniques uses the space between the names to identify where to split.

Please assume that your data starts with C14 cell.

Finding the First Name

Finding the Last Name

Finding the Last name when a Middle name is present

The formula above cannot handle any more than two names. If there is also a middle name, the last name formula will be incorrect. To solve the problem you have to use a much longer calculation.

Insert Zero (0) before any number in Excel
Zum Original Blog Automation Guru

Have you ever tried to insert 0 before sequel of numbers in Excel ? You think that what’s the use of appending 0 before sequel of numbers because in mathematics 123 is same as 0123 but there are some areas where these two numbers are treated as different number. As in banks, if you have an account, definitely you have an account no. account no. is an entity which distinguishes you from all others so it must be unique to identify you. For example account no. 123 is different from 0123. Both the account no. belongs to two different persons, if you write 0123 in the cell of excel sheet, 0 will be automatically removed as we move to the next cell.

Running Total in a Table
Zum Original Blog Automation Guru

Often times in Excel you need to get a running total of values.  In previous versions of Excel (2003 and prior) you could use something like this…

=SUM($B$2:B2)

This assumes your data is in column B and starts in row 2 and goes down.  While this still works in future versions, it can give erroneous results when working with Tables.  These were introduced in Excel 2007 and can make life much simpler.  They are a fantastic tool, but there are some drawbacks.  One of which are running totals.  The problem comes into play when you start adding rows, sometimes the formulas do not always update the way they should and your formulas will remain static and not update.  This is but one of a few issues revolving around Tables.  For this post I’m going to focus on the running total issue of Tables.

Microsoft Natural Keyboard Shortcuts
Zum Original Blog Automation Guru

Microsoft Natural Keyboard Shortcuts :

• Windows Logo (Display or hide the Start menu)
• Windows Logo+BREAK (Display the System Properties dialog box)
• Windows Logo+D (Display the desktop)
• Windows Logo+M (Minimize all of the windows)
• Windows Logo+SHIFT+M (Restore the minimized windows)
• Windows Logo+E (Open My Computer)
• Windows Logo+F (Search for a file or a folder)
• CTRL+Windows Logo+F (Search for computers)
• Windows Logo+F1 (Display Windows Help)
•  Windows Logo+ L (Lock the keyboard)
•  Windows Logo+R (Open the Run dialog box)
•  Windows Logo+U (Open Utility Manager)

Zum Original Blog
Autor: Automation Guru

Welcome Message
Zum Original Blog Automation Guru

Welcome to AutomationGuru.org. It provides a free articles on excel, access and a community forum dedicated to provide best practices and help on Microsoft Office softwares including Excel, Access, Word, PowerPoint and other applications. If you want to ask any questions or help other members out, please join our community, the registration is free and takes only few seconds.

You can tap the collective wisdom of other Excel, Access, Word and PowerPoint users by posing questions in the AutomationGuru.org forum, or browsing through any of the topics to improve your knowledge of MS-Office applications, thereby bringing more efficiency to your projects, reports and deliverables.

Zum Original Blog
Autor: Automation Guru

excel [Flickr]
Zum Original Blog Automation Guru

Automation Guru posted a photo:

Zum Original Blog
Autor: Automation Guru