Create a dashboard or control panel with Excel

A dashboard with Excel also called a control panel, can be assimilated to the dashboard of a car, which reflects the different situations of the fuel level, speed, lights … and helps you drive.

In the company, this type of graphic representation serves to give a global and quick vision of business activity and helps when making decisions.

In an intuitive and visual way it allows to reflect the situation of the company at various moments of it.

In Excel or in any spreadsheet you can build your own dashboard.

Next we are going to see a simple exercise to build a Dashboard with the data of the profit and loss account of a company.

Steps to create a dashboard yourself

• Open a blank spreadsheet .
• On the different sheets of the book you will change the name to the following:

Sheet1: Control panel (In cell A1 of this sheet write Control Panel again and insert your company logo).

Sheet 2: Data.

• Then on the Data sheet copy and paste the information that gives you the income account of your accounting program.

If you want you can take these data to perform the exercise:

Suppose the following sales of products from an appliance store:

Write the data in columns A and B of the sheet called Data.

• Then select the data and click on the Insert tab and then within the Graphics in Columns workgroup and choose 3-D Columns.
• The graph appears on the sheet you are on and now you are going to Cut and Paste it on the sheet called Control Panel, specifically in cell A2.
• Similarly write on the Data sheet those related to business expenses, you can get them from your accounting program or you can use the following:

• Select, create the graph, cut and paste like you did in step 4 above, in this case choose for example a circular graph.
• Finally you are going to reflect the profit or loss in the period, for this you take the sum of the previous amounts in such a way that the sum of sales is 2,000 and that of expenses is 1,750.
• Write in the cells of the Data sheet:
• Create another graph and place it next to the previous ones in the Control Panel sheet.

You already have a first dashboard or control panel in which you can show at a glance the situation of the company in terms of income, expenses and level of profit.

Now you can edit the graphics and give them a more personalized format.

Create and use a Macro

You will have heard a lot about Macros and you will also know that they are a great help in achieving greater productivity in your work.

A Macro is a Macro a set of actions performed in a spreadsheet that will be recorded in Excel so that simply by clicking on the created macro, it is executed and performs the previously indicated set of actions or steps.

A Macro can be created fundamentally in two ways, either using the VBA programming language , Visual Basic for Applications , or you can simply record without having any programming knowledge simply by performing a sequence of actions.

In short, macros are used to automate repetitive tasks that you want to record because you do them regularly .

You are going to create a simple macro as an example that consists of each time you execute it, the words Spreadsheets About appear in the indicated cell.

For this, the steps to follow are as follows, go ahead, they are short and easy: (it will also be interesting to read: Save macros in Excel).

1.- Open an Excel spreadsheet and locate where to work with the Macros.

If your version is Excel 2007 or Excel 2010, first of all you have to activate so that the Developer tab is displayed. To do this, click on the Office Button, then on Excel Options (bottom right), activate with a tick Show Developer tab on the ribbon, OK.

If you have Excel 2003 , you don’t have to do the previous step, you have the Macro option in the tools menu.

Start recording the Macro

2.- Start recording the Macro.

If you are in Excel 2007 or 2010, click on the Developer tab and then on Record Macro, in Excel 2003 the same but accessing from the Tools menu, Macro .

The first thing you have to indicate is the name of the Macro, in this case write MIMACRO.

Then it asks for a Shortcut , we leave this field blank.

After Save Macro in: You unfold and choose This book, finally you can add a brief description that explains what this Macro is going to do, for now do not write anything.

After pressing the Accept button, all the movements you make are recorded.

Actions to record in the Macro

3.- Actions to record in the Macro.

You go to A1 and write About Spreadsheets, press Enter and hit the Stop Recording button represented by a blue square in some black versions in others.

4.- Run the Macro.

Delete the contents of cell A1.

Press the Macro icon , in the window that opens choose the created macro, remember that its name is MIMACRO, and click on Run .

In cell A1 you left blank, the message: About spreadsheets reappears.Delete the Macro

5.- Delete the Macro.

As this has been by way of example, now you are going to delete or delete the Macro.

Click on the Macros button, having the macro to be deleted selected, click on the Delete button, a window will appear asking you: Do you want to delete the MIMACRO macro?, Choose Yes .

To check it open the Macros dialog box again and you will see that there is no macro in it.

More Macros Facts

6.- More information about Macros.

To give the name to a Macro you have to take into account that no blank spaces are allowed in the name, you can solve it using the script.

If you want you can establish a keyboard shortcut for your Macro indicating a key combination that will perform the action established in the Macro, you have to indicate this in step 2 when it asked you for a letter for the Shortcut , but note that it does not have to be an existing combination in Excel because it changes it.

 

About Author:-

Reazur Rahaman is a professional copywriter with a mission to add value to his reader’s. He always try to keep reader’s attention and engagement with his article. With an MBA and an English degree, He has the SKILLS for copywriting and marketing needs. EDITOR for a magazine, PUBLISHED writer, and PROFESSIONAL copywriter. EVERY word needs an architect.