Category Archives: Jobs

More Dynamic Excel King

Posted on by
Reply

Tips from Excel King

Make your macros more dynamic!

Today's tip is about how to easily create a macro that allows user select the Excel file / text file / ASCII file etc to be opened. You may also examples of how you can jump between multiple open files.
When you create macros that perform some more advanced operations, it is always good to think dynamically – ie how outset builds flexibility into your macros.

A clear advantage of this approach is that the Excel files as the macro import not need to have the same name on each run of the macro.

The procedure is as follows:

1. Take in the name of what file.
The method GETOPENFILENAME in VBA open dialog “Open File” or “Open File”. This box allows the user to easily point to a wise file.

2. Read in the name (path) in a variable.

3. Use variable to open file.

4. Carry it as desired with the file.

All of the above is managed via simple VBA code that you can easily include in your macros.

Maybe you get to experiment with the code a few times before you fully understand the procedure, but once you've learned the example, you will surely have a significant benefit in the future.

You can find the sample code in the section on GetOpenFilename on Excelkungen.com.

He is truly the king of Excel…Excel king..
Be Careful Out There
Anders

Efficient everyday macros in Excel

Posted on by
Reply

More good advice from Exelkungen.

Complicated Excel Macros in all its glory…

… but many of the great efficiencies in simple macros that eliminate repetitive tasks!

Becoming better at Excel has a lot to get better at using Excel more effectively, and perhaps above all to understand WHAT should be streamlined and what precisely your time wasters are.

In many cases, repetitva operations are simplified by using simple macros that you connect to the simple keyboard shortcuts (which furthermore can be performed with only one hand, som t ex CTRL + in).

Below are examples of a few simple “effectiveness macros. For you to be more efficient at your job everyday, you must “examine” yourself and learn which operations are the most time-consuming in your case.

Alternating left / right position

Sub VansterHoger()
If Selection.HorizontalAlignment = xlRight Then
Selection.HorizontalAlignment = xlLeft
Else
Selection.HorizontalAlignment = xlRight
End If
End Sub
Paste formats

You copy (CTRL + c) a cell / range of cells with the format you wish to apply to a different cell range. Then select the new field and run the macro.
Sub Paste Formats()
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
End Sub
If you have not started with macros yet?

Then you look at the course sections in Macro School, a number of video tutorials showing how to get started with Macros and VBA in Excel.

Fun, oh eh fun…

Posted on by
Reply

…it will be the first day on the job after 5 wonderful holiday week with family. Do not…'d rather watch Zlatan camp today when Wilma will kick off its annual week camp on Veddesta Vallen. Want soooo much rather be there…

Be Careful Out There
Anders

Excel King the tricks of the presentations

Posted on by
Reply

Several tips Excel King. This time in the form of a newsletter with a bit of beauty advice.

NEWSLETTER
You have suttit x number of hours with your Excel report… and soon it shall be distributed to all your colleagues. Furthermore, you have triple checked all the numbers and formulas so that all values ​​are correct. But you also have triple checked HOW figures presented?

Does your Excel file get maximum impact?
In my daily work I have encountered many Excel files that I only barely been able to make me as the presentation itself has been heavily opedagogisk. Immunologic arrays, heavily over-worked format and colors… I simply have not been able to make me content in a quickly and intuitive means.
Early on I realized that the presentation of an Excel file in principle is as important as the content itself. And that the goal of an Excel file that is distributed reasonablymust be it to get as large an impact as possible.
Having said this, I want to of course add that good content is a prerequisiteto it at all should be relevant to send an Excel file.
Men, if the presentation is poor so is the risk that the recipient does not come to terms with the content, no matter how relevant and interesting your numbers are. The result is thus that our Excel file did not get the maximum effect we desired.
It may be difficult to optimize the presentation of Excel. First, it's about trying to give the document a logical structure. Firstly, you have to realize that all recipients are unique. Below I describe two basic rules I myself always working behind in the distribution of Excel Files.
1. The most important content first
Put the most important information / table at the top left, and preferably on the first (vänstersta) tab. Studies show that we first looking on the upper left corner of the screen. Add hence the information to the right so that The key is in the reader's eye-catcher. Internet marketing is working a lot with the structure and how the visitor will accept the contents of the screen.
2. Simple and consistent format
Everyone has different preferences when it comes to color and shape. So you have to find a “lowest common denominator”. Furthermore, it is important to be consistent in the use of format. Otherwise impedes us for the recipient to understand the content.

Consider receiver before you send your Excel file!

It can be difficult to design workbooks that will be distributed, and also there is obviously no right or wrong. But if you continually have the receiver in focus, then I believe you will pretty much.

Excel King is really Excel King…

Excel pointers again

Posted on by
Reply

Ok, Now you can SUMIF then just move on to Excel King the next video cool (if the Excel dig…Thus, when it becomes cool:-) )

The relatively unknown formula SUMPRODUCT allows you to easily summarize by multiple criteria, which is invaluable when you quickly want to extract a specified amount from a larger dataset. The formula is really designed for a purpose (multiplying the values ​​of the matrices) but via an alternative use that will serve the purpose to summarize by multiple criteria in a most extraordinary manner.

Excel, cracked financial formula!

Posted on by
Reply

After a few weeks of searching on the internet and especially YouTube så fann jag otroligt många klipp om excel. There are a bunch of real fanatics out there but what I had expected?

Anyway, I found a formula, budget files that will produce the original budget, outstanding expenses, final cost estimates and the monitoring of these.

Formula was SUMIF which proved to be extremely useful. Hard to explain here in detail how to do it depends on how you set up your budgetfil but I recommend going into Excel King website you will get lots of cruel the tricks of Excel that suddenly makes you King of your work which can be a pain when all will come to you and ask for advice all the time…or it will not be difficult.

Formelns argument

=SUMMA.OM (area;conditions;Total area)

  • Area: Cellområdet som vi ska leta efter villkoret i
  • Conditions: Själva villkoret. Can be a such (t ex 8), in Evaluation (t ex >7) or text (such as "Spoons").
  • Summary Area: Cellområdet som vi ska summera

Important: Remember that "Area"And"Summary Area” should include the same lines (but different columns of course).

Excel owns and yes…it's a little crazy to get involved with it during the holiday.

ASu