Microsoft Excel articles
Enjoy these exclusive articles written by our guest experts - exclusively for us. We are sharing this useful information about Excel here for the users of our file repair tool, ExcelFIX.
In this article we will take a closer look on how we can create a basic data report with VBA for Excel 2003 and 2007. With a basic report I refer to solutions where data is, for instance, retrieved from a central database, which is then placed in a worksheet.
This Excel 2003 macro code copies charts that exist on individual sheets onto one sheet, sizes them, and previews for printing. It then blanks out the sheet used for consolidation so it is ready for the next time. It works quite well for consolidating multiple charts onto one sheet for printing purposes.
You can use the cursor keys to cycle through the elements in a chart. The up and down keys move through the main elements whilst the left and right can be used to move through the items in a element.
If you are over a certain age, you will remember when all computer reports were produced by the MIS department using COBOL and a high-speed IBM Line Printer. These reports always printed on paper called "Greenbar". Four rows were shaded in green, then four rows were shaded in white.
Using VLOOKUP, HLOOKUP, INDEX, and MATCH in Excel to interrogate data tables Lookup tables are fantastically useful things in Excel. I remember when someone showed me for the first time how to build a data table and perform some simple lookups on it. For the first time, I began to realise just how powerful Excel could be in the right hands.
Introduction I have often come across lists of names and addresses in Microsoft Excel where the first name and the last name is entered in one field as "FULL NAME." I know all the database administrators and CRM admins will be groaning at the sight of another database that has first and last names in the same column. Recently, I was given a database with over 35,000 names where the first and last name were in a single column. Fortunately with MS-Excel, you can very quickly fix this problem.
Macro Purpose: - Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.
Like many users of Excel, I've found a few really useful features which I use quite often.
You probably know how to create a basic pivot table. Here are some obscure pivot table tricks which will help you get the most out of pivot tables.
Macros, those do-it-yourself software programs, rank among Microsoft’s most useful tools. They automate many computer tasks that you otherwise would have to execute manually—from the simple task of creating customized worksheets to the very complex tasks of exporting journal entries in Excel into an accounting package and creating reports in Word.
Analyzing performance data in Excel using array formula and dynamic range name methods.
On first inspection the shapes of the built in line markers appear to be very limited.
Moving beyond a dependence on array formulae in Excel It's something of a rite of passage, really, the day when the intermediate Excel user discovers array formulae. If you haven't got to that stage just yet, stop reading now, but keep this article handy. When you get to the point (and you will) when you can't imagine life again without array formulae, take out this article and read it.
This article contains code examples to print worksheets to PDF files, using PDFCreator. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDFCreator from Sourceforge here .
There are advantages and pitfalls when creating multi-table queries - some obvious and some not so obvious. In this article, we will first examine the pitfalls of multi-table queries and then learn how to use them to our advantage.
In this lesson we will look at how to combine the information in multiple worksheets using one of Excel's least used features: the conditional functions . The power these functions bring to the user is enormous. They let you take the information in one sheet and analyse it against the information in another sheet.
Ever wonder how large corporations ran their accounts before the days of accounting software? If you guessed Spreadsheet Software, give yourself a pat on the back. It wasn't called MS-Excel back then (Bill Gates was still in his nappies… or just growing facial hair) – it had other names; like VisiCalc (for those who were around at that time) Lotus (I was surely around for this one) etc.
Many times after macros have been recorded or written users want to remove them as they are no longer required, but they cannot get rid of the 'macro warning' pop-up that appears each time the workbook is open, (see below). (You should only follow these instructions if you have no further need for the macros or UserForms!) .
Why Excel is missing a charting feature and how that feature can be recreated using VBA.
You may have heard about Classes in VBA (and other languages) but not know what they are or how to use them. Let's find out!
Bill Jelen has published a volume of 277 Excel mysteries solved. Presented here are a smattering of tips from the book.
Both column and bar charts give you the option to invert the area colour of any bar that has a negative value. What is not clear though is how to control the colour used for the negative bars.
Name field reconcatenation in Excel explained through building worksheet formulae and custom VBA functions.
Cells with comments have red indicator triangles in the upper-right corners, usually the comments are hidden but appear if the mouse pointer is rested over a particular cell.
One frequent requirement in Excel is to check for duplicates while you are entering data into a spreadsheet.
Continuing from the first example for de-duplicating cells in Excel, we see here a different problem that requires a different solution.
While working with databases in Microsoft Excel - yes, you heard me right... Excel is perhaps the best database tool that you can START off with - it gives you the flexibility to explore while not having to deal with all the constraints placed by traditional database software. If you are about to import some data into your CRM system, it pays to have a look at the data inside Excel, just to make sure that everything looks OK before proceeding.
In September, Bill Jelen will publish a volume of 277 Excel mysteries solved. Presented here are a smattering of tips from the book.
Every business needs customers to sell to. The more prospects you have in your database, the more the chances of closing a sale. The internet is full of directories that contains the names and contact details of potential customers. The problem that most business face is getting this contact information into their database.