Tips and Tricks: Save Time in Controlling Your Google Sheets

If you think you cannot learn anything new every day, think again.

This actually applied to my most recent exercise conducted for #teammalaysia when I was asked to help out to track on our recent t-shirt ordering and buying “expedition”.

You can actually read the summarised progress here on how it how far it has progressed.

Over here, I would like to share what I have learned in my most recent attempt to make full use of Google Sheets (which can be viewed in public for the benefit of transparency).

@aikido.hung would have been proud of me, but then again, to him it is just easy peasy; because he is the master of Spreadsheets!

I would believe that most of the time we are very much used to the basic use of Google Sheets (or Excel Spreadsheets), and most of the time (like me, maybe not you) would probably just manually fill in everything that needs to be filled in; and this usually could cause human error compilation.

Especially when your data is very important for the supplier to read through later.

(oh no no no, the supplier will not see this; but will see the summarised version based on what we have collected. The sheet is still in progress of becoming better)

Anyways

Here are some of the things that I have learned recently that perhaps, just perhaps could be of great use for you in the future!

Tip 1: The If statements

The IF statement is actually very useful especially when you are looking into different types of responses.

I however did use a very manual style to do this (until I perfect it in the future), but if you noticed that different “X” mark in the cell produces different cash value.

A very good example is the application if the order requires express postage poslaju .

A t-shirt that is 2XL and below all comprises of MYR 23 in total for full customised printing of a t-shirt with at least 6 colours on it; but if a buyer request for a postage service, if you do not use a formula to do so, you will end up having doing all the calculations manually, where you can make mistakes.

Therefore, in every cell in column N (Total Price), this rule was applied:

I figured a screen capture will work better as there are colour codes to it

This applies to those who used cash sales, and if there is and indication of “Y” in cell L (That includes poslaju services) it will add an extra MYR 9 into the service charge (including packaging)

Now that, is actually quite simple and most intermediate users would have known this formula.

I can assure you, I have plenty of admin staffs who still know nuts about this, and they use garbage in garbage out method to manually key in. The most they would use is SUM and addition and subtraction.

Now come the INTERESTING PART

Tip 2: Colour coding your cells based on certain rules IN A RANGE.

Yes, because most people would have done the cell-by-cell custom colouring; but to save time, there is this really cool feature in Google Sheets (I have not checked Excel sheet) that will help you to colour code your cells based on the criteria you already set.

1. Where to find it?


When you right click your cell, you would already see that there is a “Conditional Formatting)” feature stated at your Google Sheets. All you have to do is to click on it and it will bring you to a special window at the side for you to set it up.

2. Specify your range


Usually when you click a single cell, it would just show you just one cell; but if you would like to specify an entire column to look at, you will need to type this (and it will automatically formulate for you when you press “Done” later)


Leave the to range number unfilled so that the system will intelligently fill up the default range for you; otherwise you will need to specify yourself.

3. Tell Sheets what you want

This is where the magic happens. At the value of formula you would need to write in your specific thing to look into; BUT, so far, as I have not used the IF statement yet, you should specify a simple value only instead of complicating things because one range of cell can actually receive multiple rules to look into (sequentially)


Notice that I only specify one cell as a reference to look into ?

There were countless times I tried to set it as a range to spot for the word “Sabah”; but often times it gave me an error.
After vigorous searching thanks to Presearch that gives more unfiltered search results compared to using Google search directly; I found out that somehow because of the range I have set to look on, each of the row in column U (for example) will automatically counter check each row in column R.

Notice that I put a $ before R? That is because I only want the rule to check on nothing but column R; otherwise the whole formula will go crazy because it will check the entire range including the application range U.

After that, you can just apply the style you want exactly as you wished so that it will show just like what you want.

4. Repeat step 2 and step 3 if you want more rules to apply to the same range


As I have mentioned before, you actually CAN make multiple rules for the system to check sequentially on each rule. These rules become “If statements” or “Case select” statements (for those programming junkies) so you won’t have to be frustrated to make if formulas into the format cell if… section.

I have not tried adding multiple match conditions into the formula just yet; but if you already know about it, do share your comments or even point me to your post if you already have written one.

Knowledge is power and sharing is wealth. Sometimes we keep the things we learn so much to ourselves we ended up overloading our lives with unnecessary burden when people come and try to ask you for help.

These are already quite common and humans should not race and compete with each other, but instead offer ourselves in terms of service to those who truly need help and perhaps, from that point, our validated characteristics will win the hearts of more than what we can charge them for, and that, actually generates more wealth than you can think of.

Anyways, enough with the philosophical part above. I hope from this post you have learned a neat trick or two to impress not only your bosses but make your lives easier, as much as this discovery helped me so much!

Tips and Tricks: Making Different Headers in Microsoft Word

Howdy everyone!

Every since 2 weeks ago, my office has been challenged with a lot of hurdles to get things organised; especially getting everyone to standardise their documentations together.

You see, my office deals with a lot of letters and instructions; and most of the time, although there is a pretty standard documentation template, colleagues tend to make their own design (and not getting from the main one).

And most of the time, because of that, there are many standard formatting issues, especially reference numbers (because these are all official documents addressing to recipients) often get left out or just totally done out of place.

Hence it was my task and challenge to make a standard template in Microsoft Word that will be deployed and used for many other projects we handle for clients and consultants.

Over the course of researching, I found there were several ways that could work, but is not efficient.

One of them was REF command in Fields object

The good part of using form fields are that you could actually bookmark your fields and then have another field to refer to it; but the bad part of it is that it will not update the referred information between the master field and the sub field on the fly, until you press print preview. This is very inconvenient especially for those who are creating a Save As soft-copy document into PDF files and email out because it doesn’t automatically update it.

So I went to search around and look for other alternatives.

Hallelujah to Presearch.org ! Oddly this search engine although ties to Google search results but its accuracy is way much better!

(psst! It also allows you to earn crypto while you search!)

And there is where I found a way to control it via Styles.

Using Styles to Make Reference Pointers between Document Body and Footer

According to what I have read through and watched the tutorial video, it is stated that the easiest way to control referencing via styles is to place the reference source into tables format, so that if there are any changes in the cell, the style is never effected.

I have decided to use the Microsoft Word Controls for other programming purposes, and I will make this entire reference into a customised style.

I am using Microsoft Word 2010 where it is sitting at the ribbons of the application; and I think the more recent version is also the same; and you click on the expand arrow to see more. Alternately, you can use Alt+Ctrl+Shift+S  keys (I am not certain of Mac machines) key shortcut to launch it.

So over here you will need to add a new style; a unique new style that will not be used by any other parts of the document so that whichever refers to it, will not get confused of the value inside.

Make sure the name of the style is not too common; and since I am creating a reference number value in the cell, it is unique enough because it is not frequently used by others.

So as you can see, I have decided to add an underscore to the name of the new style so that I personally will know that it is my customised style; and you can even change the font size, spacing, font types and paragraph format as well.

Once you are done, click OK.

Now, the test

So according to the video I learned from, if I create a header or a footer, as long as I point that data to the customised style, it should automatically update as well.

So at the footer, I wanted the reference number to be at the middle of the footer page; so I will just insert a basic 3 column footer and delete the sides.

After that I go to the Insert Tab and choose Quick Parts then select Field.
Note: You cannot just update the current container to the style. It doesn’t work.

So inside the Field window, StyleRef (Style Reference) is the one you need to choose from, and select the customised style you have chosen. For me, my unique customised style would be Reference_Number.

And voila! this is the content field default value that I have original made in the body of the word document.

And I have moved the table closer to the footer so that you can see that as I typed the content field, the footer updates on the fly!

So there you go! A really cool trick to make sure that your important information that needs to be at the footer automatically updates to avoid typo / spelling mistakes.

Oh one more thing!

Do you know that you can have a different first page header and footer, or you can even add in odd and even page customised footers at one go?

At the Header and Footer Ribbon section, you will see the option check box that will allow you to add on extra header footer settings into the document / template you would like to customise in order for a better control of what you want to display. Most people use New Section at page setup; (I used to do that too) but that is when you need to create more than 3 types of header and footer set, which comes in handy if you are writing your reports, proposals, thesis or a book even.

And to give you a better understanding, here is how it looks like when I have clicked Different First Page checkbox; where immediately you will see the footer has changed and the reference I created is hidden until second page is made.

I hope this entire tips and tricks process has given you a better understanding and a new thing to learn on how to make your Microsoft Word document more standardised and effective presentation with references you can avoid making mistakes.