Tag Archives: excel

Get more done – roll up your sleeves? Or get better at Excel?

Posted on by
Reply

There are many ways to get more done at work. This does not mean that all ways are equally effective and have the same efficiency…

If you sit a lot with Excel, the “roll up your sleeves” and sit even more viewing hours. Or you can focus on simply become better at Excel.

Excel King has experience in administrative and financial work in a number olilka organizations in Sweden as well as abroad. The situation is similar. Excel king sees that colleagues working more with Excel than they actually do. While they do not possess the knowledge that is related to all these “Excel hours”.

King's reflection is that both the organization and employees benefit from becoming better at Excel.

Excel is not just about numbers, but perhaps as much about text. Often accompanied our numbers of columns of text, such as personal, product name etc..

To quickly and effectively to conjure the large amounts of text in Excel is an invaluable knowledge which immediately leads to an interesting situation for those who work a lot in Excel.

The following article highlights two types of text processing:

1. Extracting text from a text string

Simple formulas that allow picking out certain parts of a text string. An example of this might be that you want särsklija country code (in a separate column) from product code SE-654248.

The ease of use formulas for these operations is called

SE: LEFT / RIGHT / EXTEXT
IN: LEFT / RIGHT / MID

When it comes to clean up such as name lists in Excel then the following formulas very useful.

One example is that we want to do if

“Efraim erik nilsson” >>> “Efraim Erik Nilsson “.

SE: GEMENER / CAPITALS / INITIAL
SE: LOWER / UPPER / PROPER


Be Careful Out There
Sunkans links

Excel – more than just numbers!

Posted on by
Reply

From Exelkungens awesome website
If you ask a random person what Excel is for something, you get probably the answer “It is a spreadsheet program”! Thus understood a program that is all about numbers and calculations.

But the fact is that many of us Excel users as frequently working with text in our spreadsheet. It could for example, include lists of names, product names, and so on.

It provides this with text handling in Excel is essential if you want to work really effectively. By you master the most important text formulas in Excel, you can quickly and easily “magic” with the amounts of text anywhere in Excel.

Text management is essentially about two things:
1: to break out text (extract),
2: to format text.

Extraction of text in Excel:

This means that, through a formula, pick a certain part of the text content in a cell.
We assume that cell A1 contains the text “sirapslimpa”.

Formula Result
LEFT(A1;5) “syrup”, i.e., the five characters vänstersta
RIGHT(A1;6) “clean”, i.e., the first five characters High
MID(A1;7;3) “lim”, ie three characters fr o m the seventh character

The Swedish version is called the Excel instead LEFT, RIGHT and MID.

Text formatting in Excel

Furthermore, you can conjure with uppercase and lowercase letters.
We assume that cell A1 contains the text “Donald Duck”.

Formula Result
UPPER(A1) “DONALD DUCK”
LOWER(A1) “donald duck”
PROPER(A1) “DONALD DUCK”

The Swedish version is called the Excel instead CAPITALS, LOWER and INITIAL.

Be Careful Out There
Anders

Excel, solution to the dynamic radnumreringar…I often use in my work

Posted on by
Reply

Here's a supertipps of dynamic line numbering.
How often have you not a column with row numbers and then to remove a line or add a line and then the line numbering destroyed and one must “donation of” entire table.

Here's a damn good tips to fix this:

So when you want to display a long list that you delete or add rows on , using the formula:
=ROW()-n (where n is the number of lines before the first row of the list,)
So, t.ex, if you have a list of names in column B, starting in cell B5 and you want line numbers in column A, it should be following =ROW()-4 In cell A5, and then copy the formula down the entire column A to the bottom of your list. So every fomel in column A will be =ROW()-4. ROW()-Part of the formula retunerar row number of the cell in which the formula . Since the number of lines before the first line is subtracted.

For example,: formula in cell A5 is = ROW()-4. ROW() returns 5 (IU. wheel 5) and subtraherar 4 gives 1 for your sequence numbers.
Once you delete a row in your data row numbers automatically adjusted throughout the table. When you add a row, all the row numbers automatically adjust when you have copied the formula from the cell above the new row.

There is a “plump” in this solution, however,.
If rows are deleted or that it adds to a series of (wheel 1 to 3 of this example) you will get the wrong values ​​in the row numbers. Here is the solution.

1) Choose a celll directly above the first row of your data (A4 in this example).

2) Give a name to the cell by typing a name in the Name box (IU. top) (the white bar immediately above cell A1)

3) No, use the same formula as above, replace 4 mot R0W(top).

Your new formula will now be: =ROW()-ROW(top)

 

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

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, 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