Using the Arrange Function

arrange-by-nameThe Analytics Edge add-in for Microsoft Excel lets you easily rearrange or rename columns in your report. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Why you need the Arrange function

When building a report from imported data, one of the biggest problems people encounter is that the columns change over time. Sometimes new columns appear, sometimes the column names change, and sometimes they disappear! If you are using Excel formulas, this would be a nightmare, since cell references would be pointing to the wrong data columns, and you may not notice the difference in the report until too late!

Consistent column order

Analytics Edge solves the problem with a simple wizard; import your new data into a worksheet, use the File – Read Worksheet wizard to read it, and then open the Table – Arrange wizard. Select the By Name tab and remove or reorder the columns as you require. Now if the columns change over time, the rest of your analysis is protected; if the columns you want exist, they will be arranged in the correct order. If they don’t exist or have new column names, a blank column will be inserted in their place.

arrange-renameCorrecting name changes

If column names change unexpectedly, you can easily insert a step into your macro to fix things. Select a line in the macro just after the Read Text File or Read Worksheet command, then open the Arrange wizard. On the By Position tab, identify the misnamed column and change it. Done.

Ways to use the Arrange function

Quality control – If you need a quick sanity check on manually imported data or data coming from other worksheets, insert an Arrange function step to make sure that the columns you are getting are the ones you expected. You can write the results right back out to a reference worksheet, allowing you to make simple Excel cell references to the data with confidence.

Removing clutter – downloaded data often includes a lot of extra columns that are not needed for your report, and the Arrange function lets you easily discard that extra bulk for smaller, faster workbooks.

More meaningful reports – column names are often technical, brief and not always meaningful to report recipients. They can quickly be changed into more friendly terms to reduce confusion and improve readability.

Easier charting – Excel charts are very flexible, but if your data series columns are all in the right order, you can save a tremendous amount of time. Arrange lets you tweak the order of the columns so you can use the default data selection and spend your time more effectively.

Keep it simple

Stop wasting time with Excel report maintenance. Analytics Edge makes it easy to rearrange columns the way you need them, letting you focus on your task, not the tool.