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.
Creating basic data reports with ListObjects and QueryTables
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.
Chart Copying Macro in Excel
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.
Selecting chart elements using the cursor keys
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.
Greenbar Formatting in Excel
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.
Excel Lookup Functions Explained
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.
Split Full Name Into Two Columns with First Name and Last Name
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.
Export A Range of Excel Data to a Database
Macro Purpose: - Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.
Five Top Tips for Excel
Like many users of Excel, I've found a few really useful features which I use quite often.
Pivot Tables in Excel
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.
Automate Excel Functions
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.
Excel Performance Analysis the Professional Way
Analyzing performance data in Excel using array formula and dynamic range name methods.
How to Vary the Built-in Line Chart Markers
On first inspection the shapes of the built in line markers appear to be very limited.
Beyond Excel’s Holy Grail
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.
Printing Worksheets To A PDF File (Using Early Binding)
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 .
Creating Multi-Table Queries, Advantages and Pitfalls
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.
Conditional functions in Excel
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.
Excel Tutorial, Lessons 1 and 2
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.
Removing the Macro Security Warning
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!) .
Automatic Chart Harmonisation
Why Excel is missing a charting feature and how that feature can be recreated using VBA.
Using Misc in 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!
Learn Excel from Mr. Excel 2
Bill Jelen has published a volume of 277 Excel mysteries solved. Presented here are a smattering of tips from the book.
Formatting 'Invert if Negative' for Column and Bar Charts
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.
Excel formula development in the real world
Name field reconcatenation in Excel explained through building worksheet formulae and custom VBA functions.
A User Defined Function to Review All Comments
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.
De-duplicating cells in Excel-Solution 3
One frequent requirement in Excel is to check for duplicates while you are entering data into a spreadsheet.
De-duplicating cells in Excel-Solution 2
Continuing from the first example for de-duplicating cells in Excel, we see here a different problem that requires a different solution.
De-duplicating cells in Excel-Solution 1
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.
Learn Excel from Mr. Excel
In September, Bill Jelen will publish a volume of 277 Excel mysteries solved. Presented here are a smattering of tips from the book.
How to use Excel macros to format harvested information
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.
