Get in touch with us!

Villes Productivity Tips – Saving time with Excel

Excel tips | There are countless of shortcuts in Excel that saves lots of time and energy! Here are some tips and trix to start the new year with!

One not so well-kept secret is that Excel likes patterns and tries to help you when it believes it finds one. For example, you can write January in a cell, select the cell and then grab and drag down the right corner of the cell then Excel continues with the monthly names.
This works great on numbers, dates and the like! I use the same feature later on to copy formulas from one cell to the next in rows or columns.

 

For further examples, I have filled some headlines around the months. We will generate budget per month for 4 regions.

 

Before we start filling out the table we just created, I create a separate table for the different regions and find that we should allocate our budget per number of employees within each region. This can be something else of course, number of computers, desks, trucks, percentage of pay or something completely different. For the example, I just want the regions not to have an equal share of the budget.
To create the percentage column, you see the formula to the right. The important thing is the dollar signs in the “Total” formula. By using $ in front of 4 and 7, we lock the formula when it is copied so that it does not change. L4 that stands first has no dollar sign and therefore changes to L5 when I copy the formula one line down.

 

Now we take the region’s share of the budget and add it to the table by the formula below.

 

We make up how our budget is spread out like this every month of the year. The only important thing now is that the sum will be 100%.

 

In the cell where January and Region 1 meet, we write the following formula to see how much of the region 1’s budget will be covered in January.

 

The problem now is that when we copy the formula to the next line, the formula just changes line. We now take 8% of the January budget instead of the full-year budget!

 

This is solved through dollar signs. By putting a dollar sign in front of C we say to Excel that the formula must not leave the column C, however, it may change row as there is no dollar sign before the row number.
For the second part of the equation, we put the dollar sign in front of the row number instead of the column letter. In this way, the formula changes column, but not row.

 

This time when we copy the formula, the calculation stops right where we want it!

 

And it works in both directions. When we go aside, the percentage does not change column but, on the other hand, it makes the total budget for the region.

 

There are 48 cells in the matrix below. Everyone counts correctly just by putting dollar signs in the formula and we get the right formula in the right cell in a matter of seconds!

 

Now we calculate the sum of all regions’ budgets per month.
We only use the sum function and merge all rows. Copy the formula downwards so that the references in the formula follow the line by row.

 

Now, for some strange reason, let us assume that we definitely want SEK 50,000,000 in the April budget. There are 2,000,000 missing there. And we want to get there by increasing the total budget, not changing a few percentage points per month.
This is a typical job for Golad seek that you will find under the Data tab!
Mark the cell to be changed, in our case SEK 48,000,000. Fill in the desired value, in our case SEK 50,000,000. Then select the cell that Excel should change to achieve the result.

 

When we press OK, Excel calculates the new value based on how the cells are connected. It does not matter that we ourselves at this point hardly remember how the cells count in relation to each other.

 

We make a control column! It is of the utmost importance that we know which months regions 2 and 4 sums are less than SEK 7,500,000 and where region 1 also ends at exactly SEK 7. We can achieve this with the formula “AND”.
In the formula below, which is separated by a semicolon for the arguments, we first say that Region 2 and 4 are lower than SEK 7,500,000, then we say that the sign to the far right in region 1 is “7”. If both of these arguments are true, the cell will say “True” and if any of them do not match it is instead “False”.

=AND(E21+G21<7500000;RIGHT(D21;1)=”7″)

 

Since that text is really boring, we can change it by inserting the previous formula in an IF statement. Copy the previous formula and insert it as the logical check in the IF statement. Then we just fill in what we want to show if the arguments are true and false.

=IF(AND(E21+G21<7500000;RIGHT(D21;1)=”7″);”Sanningsenligt”;”Lögnaktigt”)

 

I hope some of these features where news to you and that using the will save you some valuable time this year!

1 Comment

Trackbacks/Pingbacks

  1. First one out! | Cloud + Adapt = Cloudapt - […] https://www.altitude365.com/2019/01/01/saving_time_with_excel/As a certified Excel Experts I just can’t help it. There are some things that I’m sure will save…

Submit a Comment

Your email address will not be published. Required fields are marked *

Starkare tillsammans

Bolag inom både publik och offentlig sektor efterfrågar en alltmer kostnadseffektiv, platsoberoende och säker digital arbetsplats. Därför går nu Altitude 365 och Uclarity samman och bildar ett gemensamt specialistbolag.
Fortsätt på Altitude 365Kolla in Exobe

Altitude 365 + Uclarity – Årets Modern Work Partner!

Vinnaren är ett bra exempel på hur en “Born in the Cloud” ständigt förbättrar sitt erbjudande, arbetar nära och proaktivt med Microsoft för att hjälpa kunderna på deras digitaliseringsresa. Plus att vi på Microsoft verkligen ser fram mot den nya bolags-konstellationen (Altitude 365 + Uclarity) för ett “Starkare Tillsammans”.

Uclarity och Altitude 365 - Starkare tillsammans

Uclarity är specialister på digitala möten, telefoni, kontaktcenter och digitalt arbetssätt. Altitude 365 är specialister på säkerhet, mobilitet och hur bolag kan optimera resan till Microsoft365. Nu gör vi gemensam sak och bildar bolag tillsammans.

– Pandemin har tydliggjort behoven av en modern digital arbetsplats och vi har diskuterat ett samgående med Altitude 365 under en längre tid. Våra kunder har behov av specialistkompetens och tillsammans blir vi en ledande specialist inom Digital Workplace-området, säger Niklas Olsson Hellström, VD Uclarity AB.

Tommy Clark, Partner, Altitude 365, kommenterar:
– Inget bolag köper det andra utan båda bolagen får lika stora delar i det nya bolaget. Vår ledstjärna är att vi blir starkare tillsammans och att vi kan hjälpa våra kunder under hela deras resa.
Målet med sammanslagningen är att kunna hjälpa kunder med både teknik och effektiva arbetssätt.

– Det är då våra kunder får önskad effekt av sin investering i den digitala arbetsplatsen, säger Niklas Olsson Hellström.

Båda bolagen har svenska och internationella kunder från både privat och offentlig sektor. Sammanslagningen resulterar i en organisation på 50+ anställda baserade i Stockholm, Örebro och Göteborg.

För frågor, vänligen kontakta;
Tommy Clarke, Partner, Altitude 365 AB, 0703-593854, tommy.clarke@altitude365.com
Niklas Olsson Hellström, VD, Uclarity AB, 0734-198016, niklas.olsson@uclarity.com

Fortsätt på Altitude 365Kolla in Exobe